SQL-запрос не дает ожидаемого результата

Мне нужна была помощь У меня есть следующая таблица (образец показан ниже):

EmpID     | Type | timestamp           | block_id
----------------------------------------------------
    1     |  'R' | 2018-04-15 01:13:15 | AB12D
    1     |  'P' | 2018-04-15 05:13:15
    1     |  'P' | 2018-04-15 05:13:15
    1     |  'P' | 2018-04-15 05:13:15
    1     |  'D' | 2018-04-15 07:13:15
    1     |  'D' | 2018-04-15 08:13:15
    1     |  'D' | 2018-04-15 10:13:15
    1     |  'R' | 2018-04-15 13:13:00 | 1X1#1
    1     |  'P' | 2018-04-15 13:15:15
    1     |  'P' | 2018-04-15 13:15:15
    1     |  'P' | 2018-04-15 13:15:15
    1     |  'D' | 2018-04-15 14:13:00
    1     |  'D' | 2018-04-15 15:13:00
    1     |  'D' | 2018-04-15 16:13:37
    2     |  'R' | 2018-04-15 04:15:00 | __08XA
    2     |  'P' | 2018-04-15 04:20:00
    2     |  'D' | 2018-04-15 05:11:33

И я пытаюсь получить вывод следующим образом:

EmpID  | begin_timestamp     | end_timestamp      | block_id | P_count | D_count
---------------------------------------------------------------------------------
1      | 2018-04-15 01:13:15 |2018-04-15 10:13:15 | AB12D    | 3       | 3
1      | 2018-04-15 13:13:00 | 2018-04-15 16:13:37| 1X1#1    | 3       | 3
2      | 2018-04-15 04:15:00 | 2018-04-15 05:11:33| __08XA   | 1       | 1

то есть это своего рода блоки, и каждый empId может иметь несколько блоков. Таким образом, из приведенной выше примерной таблицы empID '1' имеет 2 блока (начиная со строки 1 до строки 7) и 2-й блок от строки 8 до строки 14; У empID 2 есть 1 блок, строка 14 - строка 16.

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

select CAST(timestamp AS DATE) as date, execution_id, min(timestamp) as begin_timestamp, max(timestamp) as end_timestamp, new_block_id, 
sum(case when Type = 'P' then 1 else 0 end) as P_count,
              sum(case when Type = 'D' then 1 else 0 end) as D_count
from (
    select *,
    max(block_id) over (partition by EmpID order by timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) new_block_id  from myTable
) d
group by EmpID, new_block_id, CAST(timestamp AS DATE)
order by EmpID, new_block_id, CAST(timestamp AS DATE)

Но это не работает, так как не дает точных результатов. Пожалуйста помоги!!

Источник

1 ответ

То, что вы действительно хотите, это lag(ignore nulls), но Postgres не поддерживает это.

Вместо этого вы можете использовать временную метку, отличную от NULL, и использовать ее для определения групп.

select date_trunc('day', timestamp) as date, execution_id,
       min(timestamp) as begin_timestamp, max(timestamp) as end_timestamp,
       max(block_id) as block_id,
       sum(case when Type = 'P' then 1 else 0 end) as P_count,
       sum(case when Type = 'D' then 1 else 0 end) as D_count
from (select t.*,
             max(case when block_id is not null then timestamp end) over 
                 (partition by EmpId
                  order by timestamp
                  rows between unbounded preceding and current row
                 ) as grp
      from myTable t
     ) t
group by empId, grp, date_trunc('day', timestamp)
Источник
Другие вопросы по тегам