请求编写Chart.js用的近一年月度SUM(bytes)统计SQL语句

Got it, let's refine your query to get the exact monthly SUM of bytes you need for your Chart.js visualization. The goal is to pull 12 months of data (from today's date back one year), grouped by month (1-12), and ensure every month has a value (even if it's 0, so your chart doesn't have gaps).

Key Requirements Recap #

• Filter data to only the last 12 months
• Group results by calendar month (1 = January, 12 = December)
• Return a total byte count for each month, including 0 for months with no traffic

Solution 1: Full 12-Month Results (Recommended for Chart.js) #

This approach uses a common table expression (CTE) to generate all 12 months, then left-joins your billing data to ensure every month appears in the output—even if there's no traffic that month. We'll use COALESCE to replace NULL (no data) with 0.

For MySQL/MariaDB

WITH months AS (
    SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT
    m.month,
    COALESCE(SUM(ia.bytes), 0) AS total_bytes
FROM months m
LEFT JOIN ip_accounting ia
    ON MONTH(ia.timeanddate) = m.month
    AND ia.timeanddate >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY m.month
ORDER BY m.month;

For PostgreSQL

WITH months AS (
    SELECT generate_series(1, 12) AS month
)
SELECT
    m.month,
    COALESCE(SUM(ia.bytes), 0) AS total_bytes
FROM months m
LEFT JOIN ip_accounting ia
    ON EXTRACT(MONTH FROM ia.timeanddate) = m.month
    AND ia.timeanddate >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY m.month
ORDER BY m.month;

Solution 2: Simplified (Only Months With Data) #

If you don't mind missing months in your output (your Chart.js graph will skip those months), you can use this simpler query:

MySQL/MariaDB

SELECT
    MONTH(timeanddate) AS month,
    SUM(bytes) AS total_bytes
FROM ip_accounting
WHERE timeanddate >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY MONTH(timeanddate)
ORDER BY month;

PostgreSQL

SELECT
    EXTRACT(MONTH FROM timeanddate)::INT AS month,
    SUM(bytes) AS total_bytes
FROM ip_accounting
WHERE timeanddate >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY EXTRACT(MONTH FROM timeanddate)
ORDER BY month;

Breakdown of Key Parts #

• DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) / CURRENT_DATE - INTERVAL '1 year': Filters data to only the last 12 months, so you don't include old traffic from previous years.
• Month CTE: Generates a list of 1-12 to ensure every month is represented—critical for a continuous Chart.js axis.
• LEFT JOIN: Ensures even months with no traffic are retained in the results.
• COALESCE(SUM(ia.bytes), 0): Converts NULL (no traffic) to 0, so your chart doesn't have missing data points.
• ORDER BY m.month: Sorts results from January to December, matching the natural order of most chart axes.

Notes for Other Databases #

• SQL Server: Use DATEPART(MONTH, ia.timeanddate) instead of MONTH() or EXTRACT(), and DATEADD(year, -1, GETDATE()) for the date filter.
• Oracle: Use EXTRACT(MONTH FROM ia.timeanddate) or TO_CHAR(ia.timeanddate, 'MM'), and SYSDATE - INTERVAL '1' YEAR for the date filter.

内容的提问来源于stack exchange，提问作者Raymond Clayton Rudman