Нарастающий итог в SQL с разрывами в данных

[Myclass]

Не согласен с такой постановкой задачи. Не может быть никакого dashboard(а) если нет таких таблиц с как _day, _week, _month итд. Ведь они нужны не только для select(a), а также для отображения на gui в например списках для фильтрации. Плюс. Например в фирмах, где продажи на выходных всегда ноль - зачем эти дни отображать?

Те. автоматическое генерирование данных может и нужно, но именно для заполнения этих dimension-таблиц, а не для real time.

[demche]

А при чем тут вообще дашборды? Во-первых, данные могут использоваться для разных целей. Во-вторых, современные BI системы позволяют набросать дашборд с фильтрами и без dimension таблиц. Если у вас база не проектировалась как хранилище данных – в ней таких таблиц и не будет.

[varanio]

Интересный стиль записи, когда запятая ставится перед полем. Странновато смотрится. Это чтобы диффы на код ревью лучше смотрелись?

[atri24]

Так удобнее коментировать строку при отладке селектов.Например, нужно убрать последний столбец в селекте - коментируешь только его:

--, p.name

[Myclass]

Интересный стиль записи, когда запятая ставится перед полем.

тоже так всегда делаю, хотя почти все редакторы как-то это не внедряют.

Комментирование одной строки, где стоит запатая и логика поля — выключает полностью это поле. Не надо перходить в другую строку и там что-либо делать. Единственое поле, где это не так работает — первое поле в Sql. Ведь там нет запятой. Но первое поле — это по-большей части чуть-ли не самое основное, которое редко когда «выключается» для показа.

[LaRN]

С рекурсивными запросами есть нюанс - ограничение на глубину рекурсии.

Для mssql глубина по-умолчанию 100, те можно так построить интервал размером не больше квартала.

[demche]

Спасибо, это довольно важное замечание, хотя и не во всех СУБД такие ограничения есть (добавил в статью). Кстати, дефолтная глубина в MySQL 8.0 – 1000 (линк), причем это была новая фича релиза.

[Kilor]

В предлагаемом подходе есть пара недостатков, которые могут стать проблемой, если размер выборки велик:

• проход по выборке делается дважды - сначала для DISTINCT, потом для OVER

• вычисление OVER ведется по уже "проджойненному" количеству записей (N товаров * M дней в интервале)

Это можно обойти с помощью LATERAL, если эффективность запроса важнее его простоты:

WITH T(dt, product, sales) AS (
	VALUES
		('2021-12-01'::date, 'A', 10)
	,	('2021-12-10', 'A', 30)
	,	('2021-12-20', 'A', 20)
	,	('2021-12-01', 'B', 20)
	,	('2021-12-12', 'B', 40)
	,	('2021-12-25', 'B', 50)
	,	('2021-12-31', 'B', 30)
	,	('2021-12-05', 'C', 50)
	,	('2021-12-15', 'C', 10)
)
SELECT
	Y.*
FROM
	(
		SELECT
			dt
		,	product
		,	sales
		,	sum(sales) OVER w
		,	lag(dt) OVER w dtp
		,	lead(dt) OVER w dtn
		FROM
			T
		WINDOW
			w AS (PARTITION BY product ORDER BY dt)
	) X
,	LATERAL (
		SELECT -- "нулевое" начало интервала
			dt::date
		,	X.product
		,	0 sales
		,	0 sum
		FROM
			generate_series('2021-12-01', X.dt - 1, '1 day') dt
		WHERE
			X.dtp IS NULL
	UNION ALL
		SELECT -- текущая запись
			X.dt
		,	X.product
		,	X.sales
		,	X.sum
	UNION ALL
		SELECT -- клонирование суммы до следующей точки/конца интервала
			dt::date
		,	X.product
		,	0 sales
		,	X.sum
		FROM
			generate_series(X.dt + 1, coalesce(X.dtn - 1, '2021-12-31'), '1 day') dt
	) Y;