Задать вопрос
@KTG
sql

Как заполнить пустые строки предыдущим значением (lag + left join)?

Хочу вывести средневзвешенную стоимость остатков клиента, за месяц которая приходится на 1 день.

Формирую временную табличку с датами за период:
WITH
    DAYS# AS
        (
            SELECT
                  LSTDATE
                FROM
                    (
                        SELECT 
                          :pBegDate + LEVEL - 1 AS LSTDATE
                        FROM dual 
                        START WITH :pBegDate <= :pEndDate
                        CONNECT BY :pBegDate + LEVEL - 1 <= :pEndDate
                    ) 
        )

Дальше имеем табличку "CUSTOMER" с одним полем - ID
ID
---
101
и делаем CROSS JOIN таблиц.
Получаем на каждую дату вывод номера клиента.
Имеем таблиц остатков BAL. которая фиксирует только изменения остатка, там 3 поля:
ID - DATE - SUM
---------------------
101 - 30.11.2017 - 50
101 - 02.01.2018 - 300
101 - 05.01.2018 - 500
101 - 07.01.2018 - 700

Для примера выберем период: 01.01.2018 - 07.01.2018.
В итоге хочу получить в итоговой выборке:
101 - 01.01.2018 - 50 - сумма взята за 30.11.2017, т.к. это будет последний остаток на начало периода. Если его не было, то 0.
101 - 02.01.2018 - 300
101 - 03.01.2018 - 300 - сумма взята за 02.01
101 - 04.01.2018 - 300 - сумма взята за 02.01
101 - 05.01.2018 - 500
101 - 06.01.2018 - 500 - сумма взята за 05.01
101 - 07.01.2018 - 700

Когда я её подключаю через LEFT JOIN разумеется я не вижу суммы на дни в которые покупки не производились. LAG на отсутствующую запись никак не реагирует, да и не можем мы определить насколько "далекую" предыдущую запись надо смотреть.

Так что сейчас выходит так:
101 - 01.01.2018 -
101 - 02.01.2018 - 300
101 - 03.01.2018 -
101 - 04.01.2018 -
101 - 05.01.2018 - 500
101 - 06.01.2018 -
101 - 07.01.2018 - 700
  • Вопрос задан
  • 1355 просмотров
Комментировать
Подписаться 1 Простой Комментировать
Решения вопроса 1
@KTG Автор вопроса
Хотя мне не особо нравится идея вложенного запроса, и есть подозрение на более изящное решение.
Но пока пришлось делать так: 

SELECT
      ds#.LSTDATE
    , cus.id
    , CASE b.date WHEN IS NULL
            THEN (
                    SELECT max(bprev.SUM) KEEP (DENSE_RANK LAST ORDER BY bprev.date)
                        FROM bal bprev
                        WHERE bprev.date < ds#.LSTDATE
                          AND SIGN(pbrev.sum) != 0)
                 )
            ELSE b.sum
      END AS OST
    FROM customer cus
    CROSS JOIN DAYS# ds#
    LEFT JOIN bal b ON b.id = cus.id
                   AND b.date = ds#.LSTDATE
    ORDER BY ds#.LSTDATE
Ответ написан
Комментировать
Комментировать
Пригласить эксперта
Ответы на вопрос 1
@x_shader
Oracle & Coffee
Заготовка без инлайн подзапросов выглядит примерно как в коде ниже. Запрос к таблице с данными здесь один.
Но надо дорабатывать напильником, чтоб цеплять первый остаток по счету, который не вошел в отчетный период.
(напр., юнионом доклеить одну строку в самый нижний подзапрос)

WITH dt_range AS (
  SELECT TRUNC (&dt_start + ROWNUM - 1) dt
    FROM DUAL CONNECT BY ROWNUM <= &dt_end - &dt_start + 1
)
,bal AS (
  SELECT 101 id_, to_date('20171130', 'yyyymmdd') date_, 50 sum_ FROM DUAL UNION ALL
  SELECT 101, to_date('20180102', 'yyyymmdd'), 300 FROM DUAL UNION ALL
  SELECT 101, to_date('20180105', 'yyyymmdd'), 500 FROM DUAL UNION ALL
  SELECT 101, to_date('20180107', 'yyyymmdd'), 700 FROM DUAL 
)


SELECT id_ --MAX(id_) OVER (PARTITION BY grp) id_
      ,dt
      ,MAX(sum_) OVER (PARTITION BY grp) sum_
  FROM (
        SELECT t1.*
              ,SUM(start_of_group) OVER(ORDER BY t1.dt) grp
           FROM (
                  SELECT bal.id_
                        ,dt_range.dt
                        ,bal.sum_
                        ,CASE WHEN bal.sum_ IS NOT NULL THEN 1 END start_of_group
                  FROM dt_range
                  LEFT JOIN bal ON dt_range.dt = bal.date_
                ) t1
        )
   ORDER BY dt
Ответ написан
Комментировать
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Тестировщик SQL
Bell Integrator • Санкт-Петербург
До 200 000 ₽
Программист SQL
АвтоАльянс • Москва
от 165 000 ₽
Программист SQL
САМО-Софт • Москва
До 220 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Сделать ТГ бот
23 апр. 2024, в 13:02
3000 руб./за проект
Разработать дизайн для тгвебапп
23 апр. 2024, в 12:52
15000 руб./за проект
В wordpress подключить модуль по рассрочке (документацию дам)
23 апр. 2024, в 12:39
4000 руб./за проект
Ещё заказы