SQL - Подсчитывать значения в столбце с использованием критериев и разбиения по

ProgramBox

SQL — Подсчитывать значения в столбце с использованием критериев и разбиения по

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

#sql #datetime #count #teradata #window-functions

#sql #datetime #подсчитывать #teradata #окно-функции

Вопрос:

У меня есть следующий набор данных, который содержит животное и дату его вакцинации. Я пытаюсь подсчитать в каждой записи, сколько вакцин было сделано питомцу за последние 90, 180 и 365 дней с помощью SQL. Я могу разобраться с этим в Excel. Вставляем приведенную ниже таблицу в ячейку Excel A1 и помещаем следующую формулу COUNTIFS(C:C,"<"amp;C2,C:C,">="amp;C2-90,A:A,A2) в ячейку D2 . Вы можете настроить 90 на 180 и 365 соответственно.

 Animal  Visit_ID    Vaccine_Date    Count_90    Count_180   Count_365
Cat 1   7/22/2017   0   0   0
Cat 2   8/1/2017    1   1   1
Cat 3   8/14/2017   2   2   2
Cat 4   8/23/2017   3   3   3
Cat 5   9/11/2017   4   4   4
Cat 6   9/30/2017   5   5   5
Cat 7   10/11/2017  6   6   6
Cat 8   10/23/2017  6   7   7
Cat 9   10/31/2017  6   8   8
Cat 10  11/6/2017   7   9   9
Cat 11  11/17/2017  7   10  10
Cat 12  11/29/2017  7   11  11
Cat 13  12/11/2017  7   12  12
Cat 14  12/25/2017  8   13  13
Cat 15  1/2/2018    8   14  14
Cat 16  1/29/2018   7   13  15
Cat 17  2/22/2018   5   12  16
Cat 18  3/9/2018    5   13  17
Cat 19  3/21/2018   5   13  18
Cat 20  4/13/2018   4   12  19
Cat 21  5/21/2018   4   9   20
Cat 22  8/27/2018   0   4   17
Cat 23  9/18/2018   1   3   17
Cat 24  10/3/2018   2   4   17
Cat 25  12/19/2018  1   3   11
Cat 26  12/22/2018  2   4   12
Cat 27  1/6/2019    2   5   11
Cat 28  1/30/2019   3   6   11
Cat 29  3/10/2019   4   6   10
Cat 30  3/26/2019   3   6   10
Cat 31  4/17/2019   3   6   10
Cat 32  5/13/2019   3   7   11
Cat 33  5/18/2019   4   8   12
Cat 34  5/25/2019   5   9   12
Cat 35  6/17/2019   5   10  13
Cat 36  7/2/2019    5   9   14
Cat 37  7/12/2019   6   9   15
Cat 38  8/2/2019    6   9   16
Cat 39  8/15/2019   6   10  17
Cat 40  8/27/2019   5   11  18
Cat 41  9/9/2019    6   11  18
Cat 42  9/17/2019   6   12  19
Cat 43  9/26/2019   7   12  19
Cat 44  10/9/2019   7   13  19
Cat 45  10/19/2019  7   13  20
Cat 46  11/12/2019  7   13  21
Cat 47  11/15/2019  7   13  22
Cat 48  11/26/2019  7   13  23
Cat 49  12/20/2019  6   13  23
Cat 50  12/31/2019  6   13  23
Cat 51  2/14/2020   3   11  22
Cat 52  3/8/2020    3   10  23
Cat 53  4/6/2020    2   9   22
Cat 54  5/5/2020    3   8   22
Cat 55  5/23/2020   3   7   21
Cat 56  6/18/2020   3   6   20
Cat 57  6/30/2020   4   6   21
Cat 58  7/16/2020   4   7   20
Cat 59  7/22/2020   5   8   21
Dog 1   3/8/2018 0:00   0   0   0
Dog 2   4/18/2019 0:00  0   0   0
Dog 3   7/1/2019 0:00   1   1   1
Dog 4   12/12/2019 0:00 0   1   2
Dog 5   12/23/2019 0:00 1   2   3

Однако, когда я пытаюсь выполнить это с помощью SQL с помощью следующего кода, он просто просматривает предыдущую строку и добавляет. Похоже, что при каждой дате вакцинации не производится обратный отсчет, как в приведенной выше формуле Excel, и я не уверен, как интегрировать оконную функцию для подсчета дат вакцинации, которые произошли в прошлом, на основе 90 180 365 интервалов при разделении животным.

 select
qry3.Animal
,qry3.Vaccine_Date
,(case when qry3.Count_90 = 0 then 0 else row_number() over (partition by qry3.Animal, qry3.Count_90_2 order by qry3.animal_rank) - 1 end) as Admit_90
,(case when qry3.Count_180 = 0 then 0 else row_number() over (partition by qry3.Animal, qry3.Count_180_2 order by qry3.animal_rank) - 1 end) as Admit_180
,(case when qry3.Count_365 = 0 then 0 else row_number() over (partition by qry3.Animal, qry3.Count_365_2 order by qry3.animal_rank) - 1 end) as Admit_365 
from

(
select
qry2.Animal
,qry2.Vaccine_Date
,qry2.animal_rank
,qry2.Count_90
,qry2.Count_180
,qry2.Count_365
,sum(case when qry2.Count_90 = 0 then 1 else 0 end) over(partition by qry2.Animal order by qry2.animal_rank rows between unbounded preceding and current row) as Count_90_2
,sum(case when qry2.Count_180 = 0 then 1 else 0 end) over(partition by qry2.Animal order by qry2.animal_rank rows between unbounded preceding and current row) as Count_180_2
,sum(case when qry2.Count_365 = 0 then 1 else 0 end) over(partition by qry2.Animal order by qry2.animal_rank rows between unbounded preceding and current row) as Count_365_2

from
(
select
qry1.Animal
,qry1.Vaccine_Date
,qry1.animal_Rank
,case when qry1.Vaccine_Date-qry1.Previous_Vaccine_Date < 90 then 1 else 0 end as Count_90
,case when qry1.Vaccine_Date-qry1.Previous_Vaccine_Date < 180 then 1 else 0 end as Count_180
,case when qry1.Vaccine_Date-qry1.Previous_Vaccine_Date < 365 then 1 else 0 end as Count_365

from
(
select
a.Animal
,a.Vaccine_Date
,b.Vaccine_Date as Previous_Vaccine_Date
,row_number() over (partition by null order by A.Animal,a.Vaccine_Date) as animal_Rank

from Animal_Vaccine a
left join Animal_Vaccine b on a.Visit_ID = b.Visit_ID - 1

) as qry1
) as qry2
) as qry3

Вот результаты SQL (опять же, я пытаюсь имитировать формулу Excel, найденную вверху):

 Animal  Vaccine_Date    Count_90    Count_180   Count_365
Cat 7/22/2017 0:00  0   0   0
Cat 8/1/2017 0:00   1   1   1
Cat 8/14/2017 0:00  2   2   2
Cat 8/23/2017 0:00  3   3   3
Cat 9/11/2017 0:00  4   4   4
Cat 9/30/2017 0:00  5   5   5
Cat 10/11/2017 0:00 6   6   6
Cat 10/23/2017 0:00 7   7   7
Cat 10/31/2017 0:00 8   8   8
Cat 11/6/2017 0:00  9   9   9
Cat 11/17/2017 0:00 10  10  10
Cat 11/29/2017 0:00 11  11  11
Cat 12/11/2017 0:00 12  12  12
Cat 12/25/2017 0:00 13  13  13
Cat 1/2/2018 0:00   14  14  14
Cat 1/29/2018 0:00  15  15  15
Cat 2/22/2018 0:00  16  16  16
Cat 3/9/2018 0:00   17  17  17
Cat 3/21/2018 0:00  18  18  18
Cat 4/13/2018 0:00  19  19  19
Cat 5/21/2018 0:00  20  20  20
Cat 8/27/2018 0:00  21  21  21
Cat 9/18/2018 0:00  22  22  22
Cat 10/3/2018 0:00  23  23  23
Cat 12/19/2018 0:00 24  24  24
Cat 12/22/2018 0:00 25  25  25
Cat 1/6/2019 0:00   26  26  26
Cat 1/30/2019 0:00  27  27  27
Cat 3/10/2019 0:00  28  28  28
Cat 3/26/2019 0:00  29  29  29
Cat 4/17/2019 0:00  30  30  30
Cat 5/13/2019 0:00  31  31  31
Cat 5/18/2019 0:00  32  32  32
Cat 5/25/2019 0:00  33  33  33
Cat 6/17/2019 0:00  34  34  34
Cat 7/2/2019 0:00   35  35  35
Cat 7/12/2019 0:00  36  36  36
Cat 8/2/2019 0:00   37  37  37
Cat 8/15/2019 0:00  38  38  38
Cat 8/27/2019 0:00  39  39  39
Cat 9/9/2019 0:00   40  40  40
Cat 9/17/2019 0:00  41  41  41
Cat 9/26/2019 0:00  42  42  42
Cat 10/9/2019 0:00  43  43  43
Cat 10/19/2019 0:00 44  44  44
Cat 11/12/2019 0:00 45  45  45
Cat 11/15/2019 0:00 46  46  46
Cat 11/26/2019 0:00 47  47  47
Cat 12/20/2019 0:00 48  48  48
Cat 12/31/2019 0:00 49  49  49
Cat 2/14/2020 0:00  50  50  50
Cat 3/8/2020 0:00   51  51  51
Cat 4/6/2020 0:00   52  52  52
Cat 5/5/2020 0:00   53  53  53
Cat 5/23/2020 0:00  54  54  54
Cat 6/18/2020 0:00  55  55  55
Cat 6/30/2020 0:00  56  56  56
Cat 7/16/2020 0:00  57  57  57
Cat 7/22/2020 0:00  58  58  58
Dog 3/8/2018 0:00   0   0   0
Dog 4/18/2019 0:00  0   0   0
Dog 7/1/2019 0:00   1   1   1
Dog 12/12/2019 0:00 0   2   2
Dog 12/23/2019 0:00 1   3   3

Ответ №1:

Я смог решить эту проблему, присоединившись к таблице самостоятельно для животных и между датами вакцинации, а затем подсчитав отдельный visit_id. Я полностью усложнил это, но был бы открыт для функции window, поскольку они мне нравятся!

 select
a.Animal
,a.Vaccine_Date
,a.visit_id
,count(distinct b.visit_id) - 1 as Count_90 --minus 1 so it as to not count itself
,count(distinct c.visit_id) - 1 as Count_180 --minus 1 so it as to not count itself
,count(distinct d.visit_id) - 1 as Count_365 --minus 1 so it as to not count itself

from Animal_Vaccine a
left join Animal_Vaccine b on a.Animal = b.Animal and b.Vaccine_date between a.Vaccine_Date - 90 and a.Vaccine_Date
left join Animal_Vaccine c on a.Animal = c.Animal and c.Vaccine_date between a.Vaccine_Date - 180 and a.Vaccine_Date
left join Animal_Vaccine d on a.Animal = d.Animal and d.Vaccine_date between a.Vaccine_Date - 365 and a.Vaccine_Date

group by 1,2,3
;

Ответ №2:

К сожалению, Teradata не поддерживает рамки окна диапазона, поэтому вы не можете делать то, что хотите, с помощью оконных функций. Решение, предложенное dnoeth с использованием одного левого соединения и условной агрегации, обеспечивает действительный обходной путь.

Однако мне интересно, будет ли пара коррелированных подзапросов работать лучше, поскольку это вообще устраняет необходимость во внешней агрегации:

 select
    v.*,
    (select count(*) from vt v1 where v1.animal = v.animal and v1.vaccine_date >= v.vaccine_date -  90 and v1.vaccine_date < v.vaccine_date) count_90,
    (select count(*) from vt v1 where v1.animal = v.animal and v1.vaccine_date >= v.vaccine_date - 180 and v1.vaccine_date < v.vaccine_date) count_180,
    (select count(*) from vt v1 where v1.animal = v.animal and v1.vaccine_date >= v.vaccine_date - 365 and v1.vaccine_date < v.vaccine_date) count_365
from vt v

Для этого запроса убедитесь, что у вас есть индекс (animal, vaccine_date) .

1. Должно быть лучше, чем эти три объединения плюс distinct, но поскольку Teradata является массивной параллельной системой, эти скалярные коррелированные подзапросы с неравенством также трудно оптимизировать. Этот индекс не поможет, если это не материализованное представление, и ему, вероятно, не будет разрешено создавать индексы в этой таблице.

Ответ №3:

Пока количество строк на значение достаточно мало, ваш подход будет в порядке. Но если количество строк в строке на значение увеличится, процессор взорвется, и ваш администратор базы данных позвонит вам 🙂

Но ваш запрос запроса может быть упрощен до единственного объединения продуктов вместо трех, предполагая, что animal / visit_id уникален:

 select
   a.Animal
  ,a.Vaccine_Date
  ,a.visit_id
  ,count(case when b.Vaccine_date between a.Vaccine_Date - 90 and a.Vaccine_Date then b.visit_id end)  as Count_90
  ,count(case when b.Vaccine_date between a.Vaccine_Date - 180 and a.Vaccine_Date then b.visit_id end) as Count_180
  ,count(b.visit_id) as Count_365
from vt a
left join vt b
  on a.Animal = b.Animal
 and b.Vaccine_date between a.Vaccine_Date - 365 and a.Vaccine_Date - 1 -- don't include current row
group by 1,2,3

Производительность может быть улучшена, если Animal является основным индексом (в противном случае также возможно создание изменяемой таблицы с этим индексом).

В зависимости от количества строк на животное и диапазона дат может быть лучше просто создать недостающие даты с помощью EXPAND , тогда вместо RANGE можно использовать СТРОКИ:

 select animal, Visit_ID, Vaccine_date
   -- EXPAND ON returned one row per day with repeated data, the CASE effectily NULLs the added rows
  ,count(case when valid_from = Vaccine_date then 1 end) over (partition by animal order by Vaccine_date rows between 90 preceding and 1 preceding)
  ,count(case when valid_from = Vaccine_date then 1 end) over (partition by animal order by Vaccine_date rows between 180 preceding and 1 preceding)
  ,count(case when valid_from = Vaccine_date then 1 end) over (partition by animal order by Vaccine_date rows between 365 preceding and 1 preceding)
from
 ( -- create the missing dates to get 1 row per animal/day
   select animal, Visit_ID, begin(pd) as Vaccine_date, valid_from
   from
    ( 
      select animal, Visit_ID
        ,cast(Vaccine_date as date) as valid_from -- seems to be a Timestamp
        -- get the next row's Vaccine_date for EXPAND ON in following step
/*      -- LAG/LEAD not supported in TD 15.10
        ,lead(Vaccine_date,1,Vaccine_date 1)
         over (partition by animal
               order by Vaccine_date) as valid_to 
 */     -- workaround for LEAD
        ,coalesce(min(Vaccine_date)
                  over (partition by animal
                        order by Vaccine_date
                        rows between 1 following and 1 following)
                 ,Vaccine_date  1) as valid_to -- replace the last row's NULL with a valid end date 
      from vt
    ) as prepare_data
   expand on period(valid_from, valid_to) as pd
 )  as expand_data
-- now remove the added dates again
qualify valid_from = Vaccine_date

Это предполагает, что для каждого животного существует только одна строка в день, в противном случае вы получите сообщение об ошибке во время РАЗВЕРТЫВАНИЯ. Затем данные должны быть агрегированы на этапе подготовки.