Как получить отчетливый счет для данных за последние x недель, но сгруппировать по неделям в redshift?

ProgramBox

Как получить отчетливый счет для данных за последние x недель, но сгруппировать по неделям в redshift?

Post author:admin
Запись опубликована:24 февраля, 2023
Post category:Вопросы по программированию

#sql #amazon-redshift

Вопрос:

У меня есть запрос ниже, который я запускаю, дает мне единый счет за month текущий месяц и dates_for_week содержит список всех дат за последнюю неделю с воскресенья по субботу.

 select COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
AND year = '2020'
-- this is for month october but week 43
and (month = '10' and dates_for_week IN  ('18', '19', '20', '21', '22', '23', '24'))

На данный момент результат, который я вижу, таков —

 Count
-----
982

Теперь я пытаюсь сделать этот запрос динамическим, чтобы он мог дать мне подсчет за последние 6 недель, как показано ниже, в качестве вывода:

 Count   Week
------------
982     W43
123     W42
126     W41
127     W40
128     W39
129     W38

Я могу преобразовать приведенный выше запрос динамическим способом, который дает мне счет за текущий месяц октябрь и предыдущую неделю, который равен 43, и он отлично работает, как показано ниже, но я не уверен, как я могу изменить его, чтобы он мог выдавать мне данные за все последние 6 недель в указанном выше формате вывода. Похоже, мне нужно также динамически изменять месяц в течение какой-то недели, чтобы получить выходные данные за последние 6 недель.

 select COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
AND year = '2020'
-- this is for month october but week 43
and (
    month = extract(month from current_date)
    and dates_for_week IN (
            select
                date_part('d',((DATE_TRUNC('week', CURRENT_DATE) - 9)   row_number() over (order by true))::date)
            from process.data
            limit 7
  )
)

Итак, что мне нужно, это за последние 6 недель и группировать по неделям, чтобы дать мне подсчет, как показано выше. Возможно ли это сделать случайно?

 and (month = '10' and dates_for_week IN  ('18', '19', '20', '21', '22', '23', '24'))
and (month = '10' and dates_for_week IN  ('11', '12', '13', '14', '15', '16', '17'))
and (month = '10' and dates_for_week IN  ('4', '5', '6', '7', '8', '9', '10'))
and (month IN ('9', '10') and dates_for_week IN  ('27', '28', '29', '30', '1', '2', '3'))
and (month = '9' and dates_for_week IN  ('20', '21', '22', '23', '24', '25', '26'))
and (month = '9' and dates_for_week IN  ('13', '14', '15', '16', '17', '18', '19'))

1. Мне пришлось прочитать ответ Филиппа Йоханниса, чтобы наконец понять, что dates_for_week это просто номер дня в дате, например, 29 для 2020-10-29. Название меня смутило. Есть ли причина, по которой вы храните год, месяц и день отдельно? Почему бы вам просто не сохранить даты как даты? Это значительно упростило бы запрос данных, а также предотвратило бы хранение в базе данных недопустимых дат (например, 2020-02-30 или 2020-29-10).

Ответ №1:

У вас есть годы, месяцы и дни в отдельных столбцах, если я правильно понимаю. Я думаю, что самый простой способ — «построить» правильный столбец даты, а затем работать с этим столбцом.

Следующий запрос должен предоставить вам последние 6 недель, включая текущую неделю.

 select 
EXTRACT(week from TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD')) week_num
,COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
and TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD') >= DATEADD(day,-42,DATE_TRUNC('week', sysdate)) 
GROUP BY 1
ORDER BY 1 desc

Однако может возникнуть проблема, поскольку недели начинаются в понедельник в redshift, поэтому может потребоваться небольшая манипуляция (добавление одного дня):

 select 
EXTRACT(week from DATEADD(day,1,TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD'))) week_num
,COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
and DATEADD(day,1,TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD')) BETWEEN DATEADD(day,-42,DATE_TRUNC('week', sysdate)) AND DATEADD(day,-1,DATE_TRUNC('week', sysdate)) 
GROUP BY 1
ORDER BY 1 desc

Отладка:

Сначала я бы начал выполнять этот запрос, чтобы проверить, правильно ли рассчитана дата

 select COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
AND year = '2020'
-- this is for month october but week 43
and TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD') between '2020-10-18' and '2020-10-24'

После этого я бы посмотрел, правильно ли рассчитана неделя:

 select 
EXTRACT(week from DATEADD(day,1,TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD'))) week_num
,COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
AND year = '2020'
-- this is for month october but week 43
and TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD') between '2020-10-18' and '2020-10-24'
group by 1
order by 1

И последнее, но не менее важное, я бы расширил временные рамки и сделал их динамическими:

 select 
EXTRACT(week from DATEADD(day,1,TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD'))) week_num
,COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
AND year = '2020'
-- this is for month october but week 43
and DATEADD(day,1,TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD')) Between DATEADD(day,-42,DATE_TRUNC('week', sysdate)) and DATEADD(day,-1,DATE_TRUNC('week', sysdate)) 
group by 1
order by 1

1. Это потрясающе. Я многому научился. Я не знал, что мы можем написать такой запрос. Похоже, я усложнил это самостоятельно. Спасибо за вашу помощь! Позвольте мне попробовать это и тоже понять.

2. Я попробовал этот запрос, но почему-то результаты не совпадают, если я сравниваю вывод этого динамического запроса с запросом вручную для каждой недели. Также в моем случае мне это не нужно для текущей недели, мне это нужно для завершения шести недель, то есть на прошлой неделе, а затем еще на 5 недель. Есть идеи, почему результаты не совпадают? Есть ли какой-нибудь способ разделить этот запрос, чтобы я мог его отладить и посмотреть, какие даты он использует для каждой недели, поскольку мне нужно с воскресенья по субботу для каждой недели.

3. Добавлена некоторая отладка и обнаружена небольшая ошибка. Я вычитал один день, чтобы получить воскресенье в субботу, но я должен был добавить один день. Не могли бы вы повторить попытку?

4. Я выполнил все ваши 3 запроса в разделе отладки — данные точно совпадают, если я использую ваш первый и второй отладочный запрос по отдельности с моим исходным запросом вручную, но я заметил несколько вещей, если я запускаю третий отладочный запрос. Первое, что я заметил из третьего запроса, это то, что он дает данные за неделю 37, 38, 39, 40, 41, 42, 43 , но мне нужны только последние 6 недель, поэтому мне не нужна неделя 37 . Во-вторых, 38, 39, 40, 41, 42 данные недели точно совпадают из третьего запроса с вашим первым отладочным запросом, но данные недели 43 не совпадают из третьего запроса с вашим первым и вторым запросом. Есть мысли?

5. Итак, я думаю, проблема связана с рассчитанным диапазоном дат. Просто дважды проверил это, я думаю, это должно быть действительно правильное условие where: and DATEADD(day,1,TO_DATE(year||'-'||month||'-'|| dates_for_week,'YYYY-MM-DD')) Between DATEADD(day,-42,DATE_TRUNC('week', sysdate)) and DATEADD(day,-1,DATE_TRUNC('week', sysdate)) — Возможно, вам придется поиграть с -42 — это означает, что вы возвращаетесь на 42 дня назад с последнего начала недели, что должно составлять 6 недель, потому что 6 * 7 = 42.

Ответ №2:

Предполагая, что у вас есть какой-то столбец даты, вы можете просто использовать что-то вроде этого

 select date_part(w, {your_date_column) as week_number,
COUNT(DISTINCT(CLIENTID))
FROM process.data
where type = 'pots'
and stype= 'kites'
and tires IN ('abc', 'def', 'ghi', 'jkl')
and comp IN ('data', 'hello', 'world')
AND year = '2020'
group by 1

Ответ №3:

Вы могли бы использовать order by и limit :

 select year, week, COUNT(DISTINCT CLIENTID)
from process.data
where type = 'pots' and
      stype= 'kites' and
      tires IN ('abc', 'def', 'ghi', 'jkl') and
      comp IN ('data', 'hello', 'world')
group by year, dates_for_week
order by year desc, week desc
limit 6;

Это предполагает, что у вас есть столбец недели, что кажется разумным предположением.

Это простой способ выполнить то, что вы хотите сделать. Я предполагаю, что при Redshift он должен иметь приличную производительность.