WedX - журнал о программировании и компьютерных науках

Генерировать даты между датой и датой после

На основании таблицы ниже

date          country   revenue
2020-01-01      US        120
2020-01-01      UK         90
2020-01-01      IT         60
2020-01-05      US        180
2020-01-05      UK         45
2020-01-05      IT         27
2020-01-09     ...        ... 
...
2020-01-13     ...        ...

Я хочу сгенерировать промежуточные даты, разделив доход на количество дней с даты в текущей строке и в строке ниже.

Пример: доход в США на 1 января 2020 г. равен 120. Следующая дата получения дохода в США — 5 января 2020 г. Между ними 4 дня. Таким образом, чтобы получать ежедневный доход, я хотел бы разделить 120 на 4 дня = 30.

Выходная таблица

date          country   revenue
2020-01-01      US         30 (120 / 4)
2020-01-02      US         30
2020-01-03      US         30
2020-01-04      US         30
2020-01-01      UK        22.5 (90 / 4)
2020-01-02      UK        22.5
2020-01-03      UK        22.5
2020-01-04      UK        22.5
2020-01-01      IT        15.0 (60 / 4)
2020-01-02      IT        15.0
2020-01-03      IT        15.0
2020-01-04      IT        15.0
...
2020-01-05      US        45.0 (180 / 4)
2020-01-06      US        45.0
2020-01-07      US        45.0
2020-01-08      US        45.0
...

Моя идея состоит в том, чтобы использовать generate_series, но для generate_series требуются две даты?

postgresql sql
11.06.2021

Ответы:


1

Как и в приведенном выше ответе, функция lead даст вам значение следующей даты. Кроме того, если вам нужно досчитать до сегодняшней даты, когда у вас нет дальнейших транзакций, coalesce может заменить отсутствующую целевую дату на CURRENT_DATE.

Данные

create table rev (rev_date date, country varchar, revenue int);
insert into rev values('2020-01-01','US',120);
insert into rev values('2020-01-01','UK',90);
insert into rev values('2020-01-01','IT',60);
insert into rev values('2020-01-05','US',180);
insert into rev values('2020-01-05','UK',45);
insert into rev values('2020-01-05','IT',27);

Запрос

with current_and_prev as (
select country, 
    rev_date, 
    lead(rev_date) over (partition by country order by rev_date) next_date, 
    revenue
from rev
order by country, rev_date)

select country, 
    dd rev_date, 
    revenue::float / (coalesce(next_date,current_date) - rev_date) revenue_split
from current_and_prev, 
generate_series(rev_date, coalesce(next_date,current_date), '1 day'::interval) dd;
11.06.2021

2 
 create table revenues(dates date, country varchar(20)   ,revenue int);
 insert into revenues values('2020-01-01',      'US',        120);
 insert into revenues values('2020-01-01',      'UK' ,        90);
 insert into revenues values('2020-01-01',      'IT'  ,       60);
 insert into revenues values('2020-01-05',      'US'   ,     180);
 insert into revenues values('2020-01-05',      'UK'    ,     45);
 insert into revenues values('2020-01-04',      'IT'     ,    27);

Запрос:

 WITH CTE AS 
 (select *,lead(dates)over(partition by country order by dates)-1 NEXTDATE
 
 from revenues r
 )
 SELECT (CASE WHEN GENERATED_DATE IS NULL THEN DATES ELSE GENERATED_DATE END)DATES,
 COUNTRY,MIN(REVENUE::FLOAT)OVER(PARTITION BY COUNTRY,NEXTDATE)/COUNT(REVENUE)OVER(PARTITION BY COUNTRY,NEXTDATE) REVENUE
 FROM CTE
 LEFT   JOIN LATERAL 
 generate_series(dates,NEXTDATE,interval '1 day' day)GENERATED_DATE
 ON TRUE
 ORDER BY COUNTRY DESC,DATES

Выход:

dates country revenue
2020-01-01 00:00:00+00 US 30
2020-01-02 00:00:00+00 US 30
2020-01-03 00:00:00+00 US 30
2020-01-04 00:00:00+00 US 30
2020-01-05 00:00:00+00 US 180
2020-01-01 00:00:00+00 UK 22.5
2020-01-02 00:00:00+00 UK 22.5
2020-01-03 00:00:00+00 UK 22.5
2020-01-04 00:00:00+00 UK 22.5
2020-01-05 00:00:00+00 UK 45
2020-01-01 00:00:00+00 IT 20
2020-01-02 00:00:00+00 IT 20
2020-01-03 00:00:00+00 IT 20
2020-01-04 00:00:00+00 IT 27

db‹fiddle здесь

11.06.2021
  • спасибо за ваше решение, однако, разве 2020-01-05 для США не должно показывать 180/4 = 45? 05.01.2020 для ИТ --› 27/4 = 6,75? 11.06.2021
  • Насколько я понимаю ваш вопрос, вы хотите распределить доход по отсутствующим датам. Доход на 05.01.2020 для США составляет 180. Если вы хотите, чтобы было 45, то как и где распределить остальные 135? Просьба уточнить. Так что я могу пересмотреть свой ответ. Просто покажите мне желаемый результат с этой строкой. 11.06.2021
  • Ответ, который вы приняли, содержит 1587 строк. Это то, что вы ищите? Доход за 05.01.2020 для США показывает 0,3441682600382409 с этим запросом. Если это правильно, то я неправильно понял вашу проблему. 11.06.2021
  • чтобы проверить правильность значений для 05,06,07,08 января, я вставил еще эти 3 строки: вставить в значения доходов ('2020-01-09', 'US', 180); вставить в значения доходов('2020-01-09', 'UK' , 45); вставить в значения доходов('2020-01-09', 'IT' , 27); получается, что и ваш подход, и подход выше верны. Однако я бы сказал, что подход, который я принял в качестве ответа, легче понять. Спасибо за ваш ответ ! 11.06.2021
  • я вообще не спорю. Какой ответ вы выберете, зависит только от вас. Я просто пытался понять. Я рад, что вы получили ответ. Проголосуйте за такой интересный вопрос. С наилучшими пожеланиями. 11.06.2021
    • Новые материалы
    Как создать диаграмму градиентной кисти с помощью D3.js
    Резюме: Из этого туториала Вы узнаете, как добавить градиентную кисть к диаграмме с областями в D3.js. Мы добавим градиент к значениям SVG и применим градиент в качестве заливки к диаграмме с..
    Я хотел выучить язык программирования MVC4, но не мог выучить его раньше, потому что это выглядит сложно…
    Просто начните и учитесь самостоятельно Я хотел выучить язык программирования MVC4, но не мог выучить его раньше, потому что он кажется мне сложным, и я бросил его. Это в основном инструмент..
    Лицензии с открытым исходным кодом: руководство для разработчиков и создателей
    В динамичном мире разработки программного обеспечения открытый исходный код стал мощной парадигмой, способствующей сотрудничеству, инновациям и прогрессу, движимому сообществом. В основе..
    Объяснение документов 02: BERT
    BERT представил двухступенчатую структуру обучения: предварительное обучение и тонкая настройка. Во время предварительного обучения модель обучается на неразмеченных данных с помощью..
    Как проанализировать работу вашего классификатора?
    Не всегда просто знать, какие показатели использовать С развитием глубокого обучения все больше и больше людей учатся обучать свой первый классификатор. Но как только вы закончите..
    Работа с цепями Маркова, часть 4 (Машинное обучение)
    Нелинейные цепи Маркова с агрегатором и их приложения (arXiv) Автор : Бар Лайт Аннотация: Изучаются свойства подкласса случайных процессов, называемых дискретными нелинейными цепями Маркова..
    Crazy Laravel Livewire упростил мне создание электронной коммерции (панель администратора и API) [Часть 3]
    Как вы сегодня, ребята? В этой части мы создадим CRUD для данных о продукте. Думаю, в этой части я не буду слишком много делиться теорией, но чаще буду делиться своим кодом. Потому что..
    Для любых предложений по сайту: [email protected]