如何编写SQL查询实现每日及前4天记录的滚动5天计数？

Hey, let's fix that rolling 5-day count issue you're facing. First, let's break down what was wrong with your original query, then walk through the correct approaches.

What's Wrong With the Original Query #

Your current approach has a few critical flaws that are throwing off the counts:

• Incorrect use of count() with date intervals: When you do count(t.timestamp - interval N day), you're just counting non-null values (which every row has here) — so you're essentially adding the same day's record count 5 times over, not actually summing records from the past 4 days.
• Unnecessary ROW_NUMBER(): The row number doesn't help with counting records across a date range; it's just adding extra computation without value here.
• No proper window range logic: You're grouping by day but not defining a window that includes the prior 4 days, so the aggregation isn't capturing the rolling scope you need.

Correct Solutions #

We have two solid ways to get the accurate rolling 5-day (current day + previous 4 days) record count, depending on whether you need per-row details or just daily summaries.

Option 1: Calculate Rolling Count for Every Record

If you want to see the rolling count alongside each individual record, use this window function approach:

SELECT
  Symbol,
  timestamp,
  high,
  DATE(timestamp) AS record_date,
  COUNT(*) OVER (
    ORDER BY DATE(timestamp)
    RANGE BETWEEN INTERVAL 4 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_5d_total_records
FROM test.rawdata
ORDER BY timestamp;

Option 2: Daily Summary with Rolling Sum

If you only need a daily aggregated view (which is more efficient for large datasets), first count daily records, then apply the rolling window:

WITH daily_record_counts AS (
  SELECT
    DATE(timestamp) AS calendar_date,
    COUNT(*) AS daily_records
  FROM test.rawdata
  GROUP BY DATE(timestamp)
)
SELECT
  calendar_date,
  SUM(daily_records) OVER (
    ORDER BY calendar_date
    RANGE BETWEEN INTERVAL 4 DAY PRECEDING AND CURRENT ROW
  ) AS rolling_5d_total_records
FROM daily_record_counts
ORDER BY calendar_date;

Quick Notes #

• Partition by Symbol if needed: If you need separate rolling counts for each Symbol (like different stocks), just add PARTITION BY Symbol inside the OVER() clause. For example:

  COUNT(*) OVER (
    PARTITION BY Symbol
    ORDER BY DATE(timestamp)
    RANGE BETWEEN INTERVAL 4 DAY PRECEDING AND CURRENT ROW
  )
  

• How the window works: RANGE BETWEEN INTERVAL 4 DAY PRECEDING AND CURRENT ROW tells SQL to include all rows where the date is within the last 4 days up to the current date, which exactly covers your 5-day rolling window.

This should give you the accurate counts you're expecting, matching the total number of records in the 5-day window for each date.

内容的提问来源于stack exchange，提问作者george135