基于start_date与end_date统计各城市每日记录数的SQL需求

Got it, let's tackle this problem. You need to count how many records cover each day for every city, based on the start and end date ranges in your abc table. First, let's clarify the expected output with your sample data—for example, city a should have 2 records on 2018-01-03 (since two rows include that date), and city b has 2 records on the same day too.

Below are solutions for popular databases:

1. MySQL 8.0+ Solution #

MySQL 8.0 and above support recursive CTEs, which we can use to generate all dates between the earliest start date and latest end date in your table. Then we join this date range with your original table to count daily records per city.

WITH RECURSIVE date_range AS (
    SELECT MIN(start_date) AS date FROM abc
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < (SELECT MAX(end_date) FROM abc)
)
SELECT 
    a.city,
    dr.date,
    COUNT(*) AS daily_count
FROM date_range dr
JOIN abc a ON dr.date BETWEEN a.start_date AND a.end_date
GROUP BY a.city, dr.date
ORDER BY a.city, dr.date;

2. PostgreSQL Solution #

PostgreSQL has a handy generate_series function that makes generating date ranges a breeze. Here are two ways to do it:

Option 1: Generate dates directly from each record's range #

SELECT 
    a.city,
    generate_series(a.start_date, a.end_date, '1 day'::interval)::date AS date,
    COUNT(*) OVER (PARTITION BY a.city, generate_series(a.start_date, a.end_date, '1 day'::interval)::date) AS daily_count
FROM abc a
GROUP BY a.city, date
ORDER BY a.city, date;

Option 2: Generate a global date range first (similar to MySQL approach) #

WITH date_range AS (
    SELECT generate_series(MIN(start_date), MAX(end_date), '1 day')::date AS date
    FROM abc
)
SELECT 
    a.city,
    dr.date,
    COUNT(*) AS daily_count
FROM date_range dr
JOIN abc a ON dr.date BETWEEN a.start_date AND a.end_date
GROUP BY a.city, dr.date
ORDER BY a.city, dr.date;

3. SQL Server Solution #

Like MySQL, SQL Server uses recursive CTEs for date generation. Note that if your date range exceeds 100 days, you'll need to add OPTION (MAXRECURSION 0) to avoid recursion limits.

WITH date_range AS (
    SELECT MIN(start_date) AS date FROM abc
    UNION ALL
    SELECT DATEADD(DAY, 1, date)
    FROM date_range
    WHERE date < (SELECT MAX(end_date) FROM abc)
)
SELECT 
    a.city,
    dr.date,
    COUNT(*) AS daily_count
FROM date_range dr
JOIN abc a ON dr.date BETWEEN a.start_date AND a.end_date
GROUP BY a.city, dr.date
ORDER BY a.city, dr.date
OPTION (MAXRECURSION 0);

4. MySQL 5.x Solution (No Recursive CTE Support) #

If you're stuck on an older MySQL version without recursive CTEs, you can create a numbers table to generate dates:

-- First, create a numbers table if you don't have one
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- Now generate dates and count records
SELECT 
    a.city,
    DATE_ADD(date_bounds.min_start, INTERVAL num.n DAY) AS date,
    COUNT(*) AS daily_count
FROM (
    SELECT MIN(start_date) AS min_start, MAX(end_date) AS max_end FROM abc
) AS date_bounds
JOIN (
    -- Generate enough numbers to cover the date range (this covers 0-999 days)
    SELECT n1.n + n10.n*10 + n100.n*100 AS n
    FROM numbers n1
    CROSS JOIN numbers n10
    CROSS JOIN numbers n100
) AS num ON DATE_ADD(date_bounds.min_start, INTERVAL num.n DAY) <= date_bounds.max_end
JOIN abc a ON DATE_ADD(date_bounds.min_start, INTERVAL num.n DAY) BETWEEN a.start_date AND a.end_date
GROUP BY a.city, DATE_ADD(date_bounds.min_start, INTERVAL num.n DAY)
ORDER BY a.city, DATE_ADD(date_bounds.min_start, INTERVAL num.n DAY);

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