如何利用Window Function重写含Count与Sum的分组统计SQL？

Alright, let's tackle this. Your original query groups by bm.book_cd_id to get the record count and total amt for each book code, filtered to FY 2017 and accounting period 1. Converting this to use window functions is straightforward once you understand how PARTITION BY works as the windowed equivalent of GROUP BY.

Option 1: Use DISTINCT with Window Functions #

This is the simplest approach—we use PARTITION BY to calculate the aggregate values for each book_cd_id, then add DISTINCT to collapse duplicate rows (since window functions return the aggregate value for every row in the partition):

SELECT DISTINCT
    bm.book_cd_id,
    COUNT(*) OVER (PARTITION BY bm.book_cd_id) AS record_count,
    SUM(fd.amt) OVER (PARTITION BY bm.book_cd_id) AS total_amt
FROM ldfods_prd.gl_fincl_dtl fd
JOIN ldfods_prd.gl_book_cd_hier_mstr bm 
    ON fd.book_cd_mstr_id = bm.book_cd_mstr_id
WHERE fy = 2017 AND acctg_prd = 1;

Option 2: Use ROW_NUMBER() to Deduplicate (Better for Large Datasets) #

If you're working with a large dataset, using DISTINCT might not be the most performant. Instead, we can use a subquery with ROW_NUMBER() to assign a unique number to each row in the partition, then filter for only the first row of each partition:

SELECT book_cd_id, record_count, total_amt
FROM (
    SELECT
        bm.book_cd_id,
        COUNT(*) OVER (PARTITION BY bm.book_cd_id) AS record_count,
        SUM(fd.amt) OVER (PARTITION BY bm.book_cd_id) AS total_amt,
        ROW_NUMBER() OVER (PARTITION BY bm.book_cd_id ORDER BY (SELECT NULL)) AS rn
    FROM ldfods_prd.gl_fincl_dtl fd
    JOIN ldfods_prd.gl_book_cd_hier_mstr bm 
        ON fd.book_cd_mstr_id = bm.book_cd_mstr_id
    WHERE fy = 2017 AND acctg_prd = 1
) subquery
WHERE rn = 1;

The ORDER BY (SELECT NULL) is a placeholder to satisfy the ROW_NUMBER() requirement for an ORDER BY clause—since we don't care about the order of rows within each partition, this works across most SQL databases (adjust if needed for your specific DB, e.g., ORDER BY 1 in PostgreSQL or ORDER BY NULL in MySQL).

Key Difference from Original Query #

Your original GROUP BY query aggregates rows into a single row per book_cd_id directly. Window functions, by contrast, compute the aggregate value for every row in the partition first, then we use DISTINCT or ROW_NUMBER() to get the single-row-per-group result that matches your original output. Both versions will return identical results to your original query.

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