Пропущенные даты для определенных идентификаторов без добавления дополнительных дат, когда этого идентификатора больше нет в базе данных SQL

Если выразить проблему словами, у меня есть огромная таблица, в которой есть подписчики и данные за каждый день. Если подписчик больше не существует, то у него больше не будет записей, то есть SUB123 больше не существует с 28.10.2021, тогда у этого подписчика будут записи каждый день до 27.10.2021. Проблема в том, что у некоторых подписчиков отсутствуют даты, и это может быть связано с выходными или другими проблемами. Я хочу заполнить эти записи нулевыми значениями, чтобы они могли быть записаны.

Текущая проблема:

ПодписчикДатаПреподобный
sub12325.10.2021256
суб45625.10.2021282
sub12326.10.2021652
sub12327.10.2021396
суб45628.10.2021132
суб45629.10.2021484
суб45611.01.202196
суб45611.02.202145

Желаемое решение:

ПодписчикДатаПреподобный
sub12325.10.2021256
суб45625.10.2021282
sub12326.10.2021652
суб45626.10.2021НУЛЕВОЙ
sub12327.10.2021396
суб45627.10.2021НУЛЕВОЙ
суб45628.10.2021132
суб45629.10.2021484
суб45630.10.2021НУЛЕВОЙ
суб45631.10.2021НУЛЕВОЙ
суб45611.01.202196
суб45611.02.202145

Моя текущая попытка:

WITH all_dates as (
SELECT
     CAST(date_column AS DATE) date_column, b.subscriber, b.date
FROM
 (VALUES
     (SEQUENCE(
      min(b.date) OVER (PARTITION BY b.subscriber ORDER BY b.date),
      max(b.date) OVER (PARTITION BY b.subscriber ORDER BY b.date),
      INTERVAL '1' DAY)
     )
 ) AS t1(date_array)
CROSS JOIN
 UNNEST(date_array) AS t2(date_column) 
LEFT JOIN MAINTABLE b 
on t2.date_column = b.date
), 
customer_dates as (
SELECT distinct a.subscriber, a.date, b.date_column
from MAINTABLE a
left join all_dates b
on a.date = b.date_column
    )
    SELECT *
    from customer_dates a

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

WITH all_dates as (
SELECT
     CAST(date_column AS DATE) date_column, b.subscriber, b.date
FROM
 (VALUES
     (SEQUENCE(
      date('2021-10-25'),
      date('2022-04-30'),
      INTERVAL '1' DAY)
     )
 ) AS t1(date_array)
CROSS JOIN
 UNNEST(date_array) AS t2(date_column) 
LEFT JOIN MAINTABLE b 
on t2.date_column = b.date
), 
customer_dates as (
SELECT distinct a.subscriber, a.date, b.date_column
from MAINTABLE a
left join all_dates b
on a.date = b.date_column
    )
    SELECT *
    from customer_dates a
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
0
22
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы можете использовать функцию lag для создания отсутствующих диапазонов для выравнивания с помощью unnest и дополнительной обработки Rev:

-- sample data
WITH dataset (Subscriber, Date, Rev) AS (
    VALUES ('sub123',   date_parse('25-10-2021', '%d-%m-%Y'),   256),
    ('sub456',  date_parse('25-10-2021', '%d-%m-%Y'),   282),
    ('sub123',  date_parse('26-10-2021', '%d-%m-%Y'),   652),
    ('sub123',  date_parse('27-10-2021', '%d-%m-%Y'),   396),
    ('sub456',  date_parse('28-10-2021', '%d-%m-%Y'),   132),
    ('sub456',  date_parse('29-10-2021', '%d-%m-%Y'),   484),
    ('sub456',  date_parse('01-11-2021', '%d-%m-%Y'),   96),
    ('sub456',  date_parse('02-11-2021', '%d-%m-%Y'),   45)
) 

-- query
select subscriber, lifted_date as date, if (date = lifted_date, rev, NULL) rev
from
(
    select Subscriber, 
        Rev,
        cast(date as date) date, 
        lag(cast(date as date)) over(partition by Subscriber order by date) prev_date
    from dataset
)
cross join unnest(
        array_except(sequence(coalesce(prev_date, date), date, interval '1' day), array[prev_date])
    ) as t(lifted_date)
order by subscriber, date

Выход:

подписчикдатаоборот
sub1232021-10-25 00:00:00.000256
sub1232021-10-26 00:00:00.000652
sub1232021-10-27 00:00:00.000396
суб4562021-10-25 00:00:00.000282
суб4562021-10-26 00:00:00.000
суб4562021-10-27 00:00:00.000
суб4562021-10-28 00:00:00.000132
суб4562021-10-29 00:00:00.000484
суб4562021-10-30 00:00:00.000
суб4562021-10-31 00:00:00.000
суб4562021-11-01 00:00:00.00096
суб4562021-11-02 00:00:00.00045

Спасибо, это то, что я ищу, скажем, для REV есть несколько столбцов, должен ли я просто повторить условие IF для всех столбцов?

Wail Ali 06.05.2022 15:50

@WailAli, если вам нужны нули, используя предоставленное решение - боюсь, да. Если полей слишком много, вы можете попытаться объединить свой подход к соединению с предоставленным (используйте его только для создания столбцов subscriber -> date).

Guru Stron 06.05.2022 16:00

Другие вопросы по теме