Невозможно сгруппировать по DATE по метке времени

Я не могу сгруппировать по дате из столбца отметки времени в запросе ниже:

 
CHG_TABLE
+----+--------+----------------+-----------------+-------+-----------+
| Key|Seq_Num | Start_Date     | End_Date        | Value |Record_Type|
+----+--------+----------------+-----------------+-------+-----------+
| 1  | 1      | 5/25/2019 2.05 | 12/31/9999 00.00| 800   | Insert    |
| 1  | 1      | 5/25/2019 2.05 | 5/31/2019 11.12 | 800   | Update    |
| 1  | 2      | 5/31/2019 11.12| 12/31/9999 00.00| 900   | Insert    |
| 1  | 2      | 5/31/2019 11.12| 6/15/2019 12.05 | 900   | Update    |
| 1  | 3      | 6/15/2019 12.05| 12/31/9999 00.00| 1000  | Insert    |
| 1  | 3      | 6/15/2019 12.05| 6/25/2019 10.20 | 1000  | Update    |
+---+---------+----------------+-----------------+-------+-----------+

RESULT:

+-----+------------------+----------------+-----------+----------+
| Key | Month_Start_Date | Month_End_Date |Begin_Value|End_Value |
+---- +------------------+----------------+-----------+----------+
| 1   | 6/1/2019         | 6/30/2019      | 1700      | 1000     |
| 1   | 7/1/2019         | 7/31/2019      | 1000      | 1000     |
+-----+------------------+----------------+-----------+----------+

Begin_Value: Sum (Value) for Max (Start_Date) ‹Месяц_Start_Date -> Следует выбрать последнюю дату с прошлого месяца

End_Value: Sum (Value) for Max (Start_Date) ‹= Month_End_Date -> Следует выбрать самую последнюю дату

SELECT k.key, 
       dd.month_start_date, 
       dd.month_end_date,
       gendata.value first_value,
       gendata.next_value last_value 
FROM    dim_date dd CROSS JOIN  dim_person k 
JOIN (SELECT ct.key, 
       dateadd('day',1,last_day(ct.start_date)) start_date , 
       SUM(ct.value), 
       lead(SUM(ct.value)) OVER(ORDER BY ct.start_date) next_value
FROM  (SELECT key,to_char(start_Date,'MM-YYYY') MMYYYY, max(start_Date) start_date
        FROM CHG_TABLE
        GROUP BY to_char(start_Date,'MM-YYYY'), key
       ) dt JOIN CHG_TABLE ct ON
        dt.start_date = ct.start_date AND 
        dt.key = ct.key
group by ct.key, to_char(start_Date,'MM-YYYY')
) gendata ON
    to_char(dd.month_end_date,'MM-YYYY') = to_char(to_char(start_Date,'MM-YYYY')) AND 
    k.key = gendata.key;

Ошибка:

start_Date is not a valid group by expression

Связанное сообщение: Ежемесячный снимок с использованием измерения даты


sql snowflake-cloud-data-platform
person django-unchained    schedule 22.07.2019    source источник
comment
dateadd не является функцией Oracle, и, помимо прочего, у вас есть лишняя запятая. Но вы не сказали, какую ошибку вы получите, когда попробуете это. Объясните, что вы пытаетесь сделать, с примерами данных и ожидаемыми результатами. См. минимальный воспроизводимый пример и как задать хороший вопрос.   -  person Alex Poole    schedule 22.07.2019
comment
dateadd - это функция Snowflake. Добавлена ​​ошибка   -  person django-unchained    schedule 22.07.2019
comment
@AlexPoole это помогает?   -  person django-unchained    schedule 22.07.2019
comment
@Matthew McPeak Это связано с вашим ответом здесь: stackoverflow.com/questions/57101141/   -  person django-unchained    schedule 22.07.2019
comment
не могли бы вы также описать логику для столбцов результатов, чтобы мы также могли помочь вам с изменениями в запросе   -  person Tajinder    schedule 22.07.2019
comment
На первый взгляд group by ct.key, to_char(start_Date,'MM-YYYY') должно быть group by ct.key, MMYYYY, чтобы использовать выражение столбца с псевдонимом из внутреннего запроса. Но я не знаю и не имею доступа к Snowflake, поэтому не могу комментировать дальше, если вам действительно не нужна версия только для Oracle.   -  person Alex Poole    schedule 22.07.2019
comment
@Tajinder Отредактировано выше   -  person django-unchained    schedule 22.07.2019
comment
@AlexPoole Это не работает   -  person django-unchained    schedule 22.07.2019
comment
Извините, я не понял логики, не могли бы вы объяснить, как значения 1700 и 1000.   -  person Tajinder    schedule 22.07.2019
comment
SUM (VALUE) для KEY = 1 и SEQ_NUM = 1 + SUM (VALUE) для KEY = 1 и SEQ_NUM = 2 = 1700. Будет отображаться начальное значение на 01.06.2019, начиная с предыдущего месяца. 1000 - последнее значение для KEY = 1. Если бы был другой SEQ_NUM в 01.06.2019, то это была бы сумма SEQ_NUM = 3 и SEQ_NUM = 4. А 15.06.2019 - последнее значение июня.   -  person django-unchained    schedule 22.07.2019

Ответы (2)


arrow_upward
1
arrow_downward

Надеюсь, я правильно понял ваш вопрос.

Вы можете проверить запрос ниже

WITH chg_table ( key, seq_num,  start_date, end_date, value, record_type ) AS
( 
SELECT 1,1,TO_DATE('5/25/2019  2.05','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 800, 'Insert' FROM DUAL UNION ALL
SELECT 1,1,TO_DATE('5/25/2019  2.05','MM/DD/YYYY HH24.MI'),TO_DATE('5/31/2019  11.12','MM/DD/YYYY HH24.MI'), 800, 'Update' FROM DUAL UNION ALL
SELECT 1,2,TO_DATE('5/31/2019 11.12','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 900, 'Insert' FROM DUAL UNION ALL
SELECT 1,2,TO_DATE('5/31/2019 11.12','MM/DD/YYYY HH24.MI'),TO_DATE('6/15/2019  12.05','MM/DD/YYYY HH24.MI'), 900, 'Update' FROM DUAL UNION ALL
SELECT 1,3,TO_DATE('6/15/2019 12.05','MM/DD/YYYY HH24.MI'),TO_DATE('12/31/9999 00.00','MM/DD/YYYY HH24.MI'), 1000, 'Insert' FROM DUAL UNION ALL
SELECT 1,3,TO_DATE('6/15/2019 12.05','MM/DD/YYYY HH24.MI'),TO_DATE('6/25/2019  10.20','MM/DD/YYYY HH24.MI'), 1000, 'Update' FROM DUAL
)
select key , new_start_date Month_Start_Date , new_end_date Month_End_Date , begin_value ,
nvl(lead(begin_value) over(order by new_start_date),begin_value) end_value
from
(
select key , new_start_date , new_end_date , sum(value) begin_value
from
(
select key, seq_num,  start_date
, value, record_type ,
trunc(add_months(start_date,1),'month') new_start_date , 
trunc(add_months(start_date,2),'month')-1 new_end_date 
from chg_table
where record_type = 'Insert'
)
group by key , new_start_date , new_end_date
)
order by new_start_date
;

Ссылка на скрипт Db: https://dbfiddle.uk/?18cfcfidd03d08c8c08c8c8c08c8f0c8c8f0c8d08f6f06f6f0c8b08b8f0c8

person Tajinder    schedule 22.07.2019

arrow_upward
0
arrow_downward

Я предполагаю, что вы получаете сообщение «ORA-00979: not a GROUP BY expression», и это связано с тем, что вы использовали TO_CHAR(timestamp_col,'DD-MM-YYYY') в предложении GROUP BY.

Добавление TO_CHAR(timestamp_col,'DD-MM-YYYY') к стороне выбора вашего оператора должно решить эту проблему и обеспечить ожидаемые результаты.

a, b, dateadd('day',1,last_day(timestamp_col)) start_date,  TO_CHAR(timestamp_col,'DD-MM-YYYY'), ...```
person nbstrat    schedule 22.07.2019
comment
Вы можете сгруппировать по чему-то, чего нет в списке выбора; это наоборот, вызывает эту ошибку. Вы проигнорировали другие выражения столбцов, которые не в группировке, и другие синтаксические ошибки. - person Alex Poole; 22.07.2019