31 lines
1.5 KiB
SQL
31 lines
1.5 KiB
SQL
-- Сигнал: день > 3 средних за предыдущие 7 календарных дней.
|
|
-- Требуется 7 дней истории и ненулевая базовая сумма; пропущенные дни = 0.
|
|
-- Целочисленное сравнение через quotient/remainder исключает overflow
|
|
-- при умножении большой SQLite INTEGER суммы на 3 или 7.
|
|
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
|
|
), baseline AS (
|
|
SELECT *, SUM(revenue_kopecks) OVER w AS previous_7_sum_kopecks,
|
|
COUNT(*) OVER w AS history_days
|
|
FROM filled WINDOW w AS (ORDER BY order_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)
|
|
), thresholds AS (
|
|
SELECT *, (previous_7_sum_kopecks / 7) * 3
|
|
+ ((previous_7_sum_kopecks % 7) * 3) / 7 AS threshold_floor_kopecks
|
|
FROM baseline
|
|
)
|
|
SELECT order_date, revenue_kopecks, previous_7_sum_kopecks,
|
|
threshold_floor_kopecks
|
|
FROM thresholds
|
|
WHERE history_days = 7 AND previous_7_sum_kopecks > 0
|
|
AND revenue_kopecks > threshold_floor_kopecks
|
|
ORDER BY order_date;
|