19 lines
930 B
SQL
19 lines
930 B
SQL
-- Календарь от первого до последнего факта, отсутствующие дни дают 0.
|
|
-- На пустой БД результат пуст; изменение первого дня NULL (нет предшественника).
|
|
WITH RECURSIVE calendar(day) AS (
|
|
SELECT MIN(order_date) FROM facts HAVING COUNT(*) > 0
|
|
UNION ALL
|
|
SELECT date(day, '+1 day') FROM calendar
|
|
WHERE day < (SELECT MAX(order_date) FROM facts)
|
|
), totals AS (
|
|
SELECT order_date, SUM(revenue_kopecks) AS revenue_kopecks
|
|
FROM daily_category GROUP BY order_date
|
|
), filled AS (
|
|
SELECT day AS order_date, COALESCE(revenue_kopecks, 0) AS revenue_kopecks
|
|
FROM calendar LEFT JOIN totals ON totals.order_date = calendar.day
|
|
)
|
|
SELECT order_date, revenue_kopecks,
|
|
revenue_kopecks - LAG(revenue_kopecks) OVER (ORDER BY order_date)
|
|
AS change_kopecks
|
|
FROM filled ORDER BY order_date;
|