使用SQL PIVOT按日期汇总带百分比的数据

Hey there! Let's solve this aggregation problem—you don't need PIVOT here, just straightforward GROUP BY with conditional aggregation will get you exactly the results you're looking for. Let's break down both queries step by step.

1. Daily Summary (Grouped by Date) #

This query groups your events by day, month, and year, calculating total records and the percentage of entries where SOMEPROPERTY = 1:

SELECT
    EXTRACT(DAY FROM EVENTDATETIME) AS DAY,
    MONTHNAME(EVENTDATETIME) AS MONTH, -- Adjust function based on your SQL dialect
    EXTRACT(YEAR FROM EVENTDATETIME) AS YEAR,
    COUNT(*) AS "TOTAL ITEMS",
    CONCAT(ROUND((SUM(CASE WHEN SOMEPROPERTY = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 0), '%') AS "SOMEPROPERTY%"
FROM your_table_name
GROUP BY DAY, MONTH, YEAR
ORDER BY YEAR, MONTH, DAY;

Dialect-Specific Adjustments: #

• MySQL: Use MONTHNAME() for full month names, DAY()/YEAR() as alternatives to EXTRACT().
• SQL Server: Replace MONTHNAME() with DATENAME(MONTH, EVENTDATETIME), use DAY()/YEAR() for date parts.
• PostgreSQL: Use TO_CHAR(EVENTDATETIME, 'FMMONTH') to get month names without trailing spaces, EXTRACT() for day/year.
• Oracle: Use TO_CHAR(EVENTDATETIME, 'DD') for day, TO_CHAR(EVENTDATETIME, 'MONTH') for month, TO_CHAR(EVENTDATETIME, 'YYYY') for year.

The ROUND() function ensures whole-number percentages like your example (75% instead of 75.00%). Adjust the decimal parameter if you need more precision.

2. Daily Summary with Category (Grouped by Date + Category) #

This query adds the CATEGORY dimension to the grouping while keeping the same aggregation logic:

SELECT
    EXTRACT(DAY FROM EVENTDATETIME) AS DAY,
    MONTHNAME(EVENTDATETIME) AS MONTH, -- Adjust function based on your SQL dialect
    EXTRACT(YEAR FROM EVENTDATETIME) AS YEAR,
    CATEGORY,
    COUNT(*) AS "TOTAL ITEMS",
    CONCAT(ROUND((SUM(CASE WHEN SOMEPROPERTY = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 0), '%') AS "SOMEPROPERTY%"
FROM your_table_name
GROUP BY DAY, MONTH, YEAR, CATEGORY
ORDER BY YEAR, MONTH, DAY, CATEGORY;

Key Logic Explanation: #

• COUNT(*) gives the total number of records in each group.
• SUM(CASE WHEN SOMEPROPERTY = 1 THEN 1 ELSE 0 END) counts how many entries in the group have SOMEPROPERTY = 1.
• Multiplying by 100.0 (not 100) ensures floating-point division (avoids integer division errors that can lead to 0% instead of the correct value).

Fixing Your Previous GROUP BY Issues: #

If you got unexpected results before, it’s likely due to:

• Forgetting to include all non-aggregated columns in the GROUP BY clause (SQL requires every selected column that isn’t an aggregate to be grouped).
• Using integer division instead of floating-point (the 100.0 fixes this).

Just replace your_table_name with your actual table name, and tweak the date functions to match your database system.

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