Кейс-запрос с группировками по сгенерированному столбцу

Question

Это пример псевдо-SQL, над которым я работаю.

select count(*) as "count", time2.iso_timestamp - time1.iso_timestamp 
as "time_to_active",
case
when ("time_to_active" >= 1day and "time_to_active" <= 5days) then '1'
when ("time_to_active" >= 6days and "time_to_active" <= 11days) then 
'2'
when ("time_to_active" >= 12days and "time_to_active" <= 20days) then 
'3'
when ("time_to_active" >= 21days and "time_to_active" <= 30days) then 
'4'
when ("time_to_active" >= 31days) then '5'
end as timetoactivegroup
from t
inner join t1 on t.p_id = t1.p_id
join timestamp time1 on t.timestamp_id = t1.id
join timestamp time2 on t1.timestamp_id = t2.id

По сути, я пытаюсь запросить в группы, где вычисляемый столбец вписывается в диапазон. Заказы между n и y днями. Проблема, с которой я в основном сталкиваюсь, заключается в создании счетчика на основе группировок.

Я могу получить запрос выбора, чтобы отобразить вычисленное значение без проблем.

Answer 1

postgresql не позволяет группировать по псевдониму, поэтому вам нужно будет повторить выражение группировки в выражении group by.

GROUP BY case
when ("time_to_active" >= 1day and "time_to_active" <= 5days) then '1'
when ("time_to_active" >= 6days and "time_to_active" <= 11days) then 
'2'
when ("time_to_active" >= 12days and "time_to_active" <= 20days) then 
'3'
when ("time_to_active" >= 21days and "time_to_active" <= 30days) then 
'4'
when ("time_to_active" >= 31days) then '5'
end 

или вы можете сгруппировать по номеру столбца:

 GROUP BY 3 

Answer 2

Игнорирование псевдо SQL (временного кода), а также игнорирование объединения таблиц, где вы ссылаетесь на неназванную таблицу T2

Так что если у вас есть несколько строк с двумя временными метками timestamp_a что раньше, чем timestamp_b то ошибка, которую я вижу у вас, заключается в том, чтобы сделать разницу в виде выбранного столбца time2.iso_timestamp - time1.iso_timestamp as "time_to_active", у вас есть два столбца, которые вам нужно сгруппировать, но вы на самом деле не хотите time_to_active в вашем ответе, в противном случае блок дел, объединяющий ответы, не имеет особого смысла.

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

create or replace table t (timestamp_a timestamp_ntz, timestamp_b timestamp);

insert into t values ('2018-11-10','2018-11-11')
   ,('2018-11-08','2018-11-11')
   ,('2018-10-08','2018-11-11');

select datediff('day', timestamp_a, timestamp_b) as time_to_active from t;

дает 1,3,34 таким образом, оборачивая их в суб-выбор (который также может быть выражен как CTE)

select case when (time_to_active >= 1 and time_to_active < 6) then '1'
          when (time_to_active >= 6 and time_to_active < 12) then '2'
          when (time_to_active >= 12 and time_to_active < 21) then '3'
          when (time_to_active >= 21 and time_to_active < 31) then '4'
          when (time_to_active >= 31) then '5'
    end as time_to_active_group
    ,count(*) as count 
from (
    select datediff('day', timestamp_a, timestamp_b) as time_to_active from t
) as A
group by time_to_active_group;

дает:

 1, 2
 5, 1

потому что у нас есть 2 строки между 1-5 и 1 в сегменте>= 31.

Другой вопрос: у вас нет обработки для отметок времени, которые являются "тем же днем", или когда время окончания предшествует времени начала, то есть когда time_to_active <= 0