Как сделать Day-of-Week анализ в SQL

Зачем DOW analysis

User behavior зависит от day. B2B usage высокая weekday, низкая weekend. E-com naoborot. Promo planning, support staffing, push notification timing — все на DOW patterns.

EXTRACT(DOW)

EXTRACT(DOW FROM '2026-05-13'::DATE)  -- returns 3 (Wednesday)

DOW convention в PostgreSQL: 0=Sunday, 1=Monday, ..., 6=Saturday.

Базовый расчёт

SELECT
    EXTRACT(DOW FROM event_time) AS dow,
    CASE EXTRACT(DOW FROM event_time)
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_name,
    COUNT(*) AS events,
    COUNT(DISTINCT user_id) AS users
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY 1
ORDER BY 1;

Weekday vs Weekend

SELECT
    CASE WHEN EXTRACT(DOW FROM event_time) IN (0, 6) THEN 'weekend' ELSE 'weekday' END AS day_type,
    COUNT(*) AS events,
    COUNT(*) / 5.0 AS avg_per_weekday,  -- 5 weekdays
    COUNT(DISTINCT DATE(event_time)) AS days_covered
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '8 weeks'
GROUP BY 1;

Hourly × DOW

Heatmap data:

SELECT
    EXTRACT(DOW FROM event_time) AS dow,
    EXTRACT(HOUR FROM event_time) AS hour,
    COUNT(*) AS events
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY 1, 2
ORDER BY 1, 2;

Plot как heatmap — clear daily / weekly patterns.

Revenue by DOW

SELECT
    EXTRACT(DOW FROM order_date) AS dow,
    COUNT(*) AS orders,
    SUM(total) AS revenue,
    AVG(total) AS aov
FROM orders
WHERE status = 'paid'
  AND order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Normalized (vs baseline)

Some days have fewer weeks of data (e.g. last Sunday). Normalize:

WITH baseline AS (
    SELECT
        EXTRACT(DOW FROM DATE) AS dow,
        AVG(events_count) AS avg_events
    FROM daily_event_counts
    WHERE DATE >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY 1
)
SELECT
    dow,
    avg_events,
    avg_events / (SELECT AVG(avg_events) FROM baseline) AS index_vs_avg
FROM baseline
ORDER BY dow;

DOW user segments

WITH user_dow AS (
    SELECT
        user_id,
        EXTRACT(DOW FROM event_time) AS dow,
        COUNT(*) AS events
    FROM events
    GROUP BY 1, 2
)
SELECT
    user_id,
    MAX(events) FILTER (WHERE dow IN (0, 6)) AS weekend_events,
    MAX(events) FILTER (WHERE dow NOT IN (0, 6)) AS weekday_events,
    CASE
        WHEN MAX(events) FILTER (WHERE dow IN (0, 6)) > MAX(events) FILTER (WHERE dow NOT IN (0, 6))
        THEN 'weekend-user'
        ELSE 'weekday-user'
    END AS user_type
FROM user_dow
GROUP BY user_id;

Частые ошибки

Ошибка 1. DOW convention differs. PostgreSQL 0=Sun. MySQL 1=Sun. ClickHouse — toDayOfWeek: 1=Mon. Verify.

Ошибка 2. Time-zone confusion. Event time UTC, DOW в local tz. Convert first.

Ошибка 3. Skewed sample. Week starts Monday. Don't slice arbitrary 7 days.

Ошибка 4. Holidays not separated. DOW pattern broken on holidays. Flag separately.

Ошибка 5. New users only weekday. Acquisition mostly weekday — biases user activity DOW pattern.

Связанные темы

• Как посчитать seasonality в SQL
• Как посчитать hour of day patterns в SQL
• Window functions advanced
• Как посчитать moving average в SQL

FAQ

EXTRACT(DOW) values?

PostgreSQL: 0=Sunday … 6=Saturday. SQL Server / Oracle differ.

Weekend как define?

Sat + Sun typical. Some cultures (Israel: Fri-Sat, ME countries similar).

DOW pattern stable?

Mostly yes, но events / promos shift. Re-validate quarterly.

Day of month vs DOW?

DOW — usage. Day of month — billing / payroll cycles.

Locale handling?

ISO 8601: Monday=1. Use ISODOW в PostgreSQL для consistency.