Как удалить повторяющиеся значения при использовании оконной функции?

Question

Я пытаюсь запустить приведенный ниже код для репликации суммы объема заказа по всем строкам. Я пытаюсь сделать это за последние 2 недели, что означает, что я должен получить 14 строк данных. Тем не менее, я получаю много дубликатов, и GROUP BY не будет работать. Может ли кто-нибудь помочь мне здесь? Спасибо!

SELECT
TO_CHAR(DATE_TRUNC('week',START_OF_PERIOD_LOCAL),'YYYY-MM-DD') AS WEEK,
TO_CHAR(START_OF_PERIOD_LOCAL , 'DY') AS DAY_OF_WEEK,
sum(ORDERS_DELIVERED) over () as total_orders
FROM p
WHERE
CITY_NAME='Hong Kong' AND
DATEDIFF('DAY',DATE_TRUNC('DAY',CURRENT_TIMESTAMP),DATE_TRUNC('DAY',START_OF_PERIOD_LOCAL)) BETWEEN -14 AND 0
GROUP BY 1,2
ORDER BY 1

примерные результаты

Идеальные результаты

Answer 1

Чтобы удалить дубликаты, нужно использовать разные. Попробуйте следующее:

SELECT DISTINCT
TO_CHAR(DATE_TRUNC('week',START_OF_PERIOD_LOCAL),'YYYY-MM-DD') AS WEEK,
TO_CHAR(START_OF_PERIOD_LOCAL , 'DY') AS DAY_OF_WEEK,
sum(ORDERS_DELIVERED) over () as total_orders
FROM p
WHERE
CITY_NAME='Hong Kong' AND
DATEDIFF('DAY',DATE_TRUNC('DAY',CURRENT_TIMESTAMP),DATE_TRUNC('DAY',START_OF_PERIOD_LOCAL)) BETWEEN -14 AND 0
GROUP BY 1,2
ORDER BY 1

Answer 2

Итак, с этим CTE, предоставляющим поддельные данные:

      WITH data(start_of_period_local, orders_delivered, city_name) AS (
    SELECT column1::date, column2, column3 FROM VALUES
    ('2022-01-27', 100, 'Hong Kong'),
    ('2022-01-27', 101, 'Hong Kong'),
    ('2022-01-26', 200, 'Hong Kong'),
    ('2022-01-26', 202, 'Hong Kong'),
    ('2022-01-18', 300, 'Hong Kong'),
    ('2022-01-18', 303, 'Hong Kong'),
    ('2022-01-17', 400, 'Hong Kong'),
    ('2022-01-01', 500, 'Hong Kong')  
) 

Мы можем воспроизвести вашу проблему, заменив CURRENT_TIMESTAMPс to_date('2022-01-27','YYYY-MM-DD')просто так это работает в будущем.

      WITH data(start_of_period_local, orders_delivered, city_name) AS (
    SELECT column1::date, column2, column3 FROM VALUES
    ('2022-01-27', 100, 'Hong Kong'),
    ('2022-01-27', 101, 'Hong Kong'),
    ('2022-01-26', 200, 'Hong Kong'),
    ('2022-01-26', 202, 'Hong Kong'),
    ('2022-01-18', 300, 'Hong Kong'),
    ('2022-01-18', 303, 'Hong Kong'),
    ('2022-01-17', 400, 'Hong Kong'),
    ('2022-01-01', 500, 'Hong Kong')  
)    
SELECT
    TO_CHAR(DATE_TRUNC('week', start_of_period_local),'YYYY-MM-DD') AS week,
    TO_CHAR(start_of_period_local, 'DY') AS day_of_week,
    SUM(orders_delivered) over () total_orders
FROM data
WHERE city_name = 'Hong Kong' 
    AND DATEDIFF('DAY', DATE_TRUNC('DAY', to_date('2022-01-27','YYYY-MM-DD')),DATE_TRUNC('DAY',start_of_period_local)) BETWEEN -14 AND 0
--GROUP BY 1,2
ORDER BY 1

дает: