如何将按不同周期分组的5条GROUPING SETS查询合并为单查询或存储过程？

Absolutely! You can merge these 5 nearly identical queries into a single, reusable solution that lets you specify the grouping period (D/W/M/Q/Y) via a parameter. Below are two solid approaches to achieve this, along with your test data formatted for easy use.

Test Data #

First, let's set up your test environment with properly formatted SQL:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-06 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 2, 102, 3,TIMESTAMP '2022-12-26 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-21 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-27 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;

Option 1: Static SQL with Parameter (Recommended) #

This approach uses a static query with a CASE statement to adapt to the selected period. It's easy to maintain, uses bind variables for performance, and avoids dynamic SQL complexity.

Replace the 'D' in the params CTE with your desired period (D, W, M, Q, Y), or use a bind variable like :p_period for application integration:

WITH params AS (
    SELECT 'D' AS period FROM dual -- Swap 'D' with W/M/Q/Y, or use :p_period
)
SELECT 
    CASE 
        WHEN p.period = 'D' THEN TO_CHAR (pu.purchase_date, 'YYYY-MM-DD')
        WHEN p.period = 'W' THEN TO_CHAR (pu.purchase_date, 'IYYY"W"IW')
        WHEN p.period = 'M' THEN TO_CHAR (pu.purchase_date, 'YYYY"M"MM')
        WHEN p.period = 'Q' THEN TO_CHAR (pu.purchase_date, 'YYYY"Q"Q')
        WHEN p.period = 'Y' THEN TO_CHAR (pu.purchase_date, 'YYYY"Y"')
    END AS period_label,
    pu.customer_id,
    c.first_name,
    c.last_name,
    SUM (pu.quantity * i.price) AS total_amt
FROM purchases pu
JOIN customers c ON pu.customer_id = c.customer_id
JOIN items i ON pu.product_id = i.product_id
CROSS JOIN params p
GROUP BY 
    GROUPING SETS (
        (
            CASE 
                WHEN p.period = 'D' THEN TO_CHAR (pu.purchase_date, 'YYYY-MM-DD')
                WHEN p.period = 'W' THEN TO_CHAR (pu.purchase_date, 'IYYY"W"IW')
                WHEN p.period = 'M' THEN TO_CHAR (pu.purchase_date, 'YYYY"M"MM')
                WHEN p.period = 'Q' THEN TO_CHAR (pu.purchase_date, 'YYYY"Q"Q')
                WHEN p.period = 'Y' THEN TO_CHAR (pu.purchase_date, 'YYYY"Y"')
            END,
            pu.customer_id, c.first_name, c.last_name
        ),
        (
            CASE 
                WHEN p.period = 'D' THEN TO_CHAR (pu.purchase_date, 'YYYY-MM-DD')
                WHEN p.period = 'W' THEN TO_CHAR (pu.purchase_date, 'IYYY"W"IW')
                WHEN p.period = 'M' THEN TO_CHAR (pu.purchase_date, 'YYYY"M"MM')
                WHEN p.period = 'Q' THEN TO_CHAR (pu.purchase_date, 'YYYY"Q"Q')
                WHEN p.period = 'Y' THEN TO_CHAR (pu.purchase_date, 'YYYY"Y"')
            END
        ),
        ()
    )
ORDER BY 
    CASE 
        WHEN p.period = 'D' THEN TO_CHAR (pu.purchase_date, 'YYYY-MM-DD')
        WHEN p.period = 'W' THEN TO_CHAR (pu.purchase_date, 'IYYY"W"IW')
        WHEN p.period = 'M' THEN TO_CHAR (pu.purchase_date, 'YYYY"M"MM')
        WHEN p.period = 'Q' THEN TO_CHAR (pu.purchase_date, 'YYYY"Q"Q')
        WHEN p.period = 'Y' THEN TO_CHAR (pu.purchase_date, 'YYYY"Y"')
    END,
    pu.customer_id;

How it works: #

• The params CTE defines your period parameter (easily swapped for a bind variable)
• The CASE statement dynamically selects the correct date formatting string based on the period
• The GROUPING SETS and ORDER BY clauses mirror the CASE logic to maintain consistency with your original queries

Option 2: Stored Procedure with Dynamic SQL #

If you need to encapsulate this logic into a callable database object, a stored procedure is a great choice. It includes input validation to prevent invalid parameters:

CREATE OR REPLACE PROCEDURE get_purchase_summary (
    p_period IN VARCHAR2,
    p_results OUT SYS_REFCURSOR
)
IS
    v_sql VARCHAR2(4000);
BEGIN
    -- Validate input parameter
    IF p_period NOT IN ('D', 'W', 'M', 'Q', 'Y') THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid period parameter. Must be D/W/M/Q/Y.');
    END IF;

    -- Build dynamic SQL based on selected period
    v_sql := '
        SELECT 
            ' || CASE 
                WHEN p_period = 'D' THEN 'TO_CHAR (pu.purchase_date, ''YYYY-MM-DD'')'
                WHEN p_period = 'W' THEN 'TO_CHAR (pu.purchase_date, ''IYYY"W"IW'')'
                WHEN p_period = 'M' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"M"MM'')'
                WHEN p_period = 'Q' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Q"Q'')'
                WHEN p_period = 'Y' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Y"'')'
            END || ' AS period_label,
            pu.customer_id,
            c.first_name,
            c.last_name,
            SUM (pu.quantity * i.price) AS total_amt
        FROM purchases pu
        JOIN customers c ON pu.customer_id = c.customer_id
        JOIN items i ON pu.product_id = i.product_id
        GROUP BY 
            GROUPING SETS (
                (
                    ' || CASE 
                        WHEN p_period = 'D' THEN 'TO_CHAR (pu.purchase_date, ''YYYY-MM-DD'')'
                        WHEN p_period = 'W' THEN 'TO_CHAR (pu.purchase_date, ''IYYY"W"IW'')'
                        WHEN p_period = 'M' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"M"MM'')'
                        WHEN p_period = 'Q' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Q"Q'')'
                        WHEN p_period = 'Y' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Y"'')'
                    END || ',
                    pu.customer_id, c.first_name, c.last_name
                ),
                (
                    ' || CASE 
                        WHEN p_period = 'D' THEN 'TO_CHAR (pu.purchase_date, ''YYYY-MM-DD'')'
                        WHEN p_period = 'W' THEN 'TO_CHAR (pu.purchase_date, ''IYYY"W"IW'')'
                        WHEN p_period = 'M' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"M"MM'')'
                        WHEN p_period = 'Q' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Q"Q'')'
                        WHEN p_period = 'Y' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Y"'')'
                    END || '
                ),
                ()
            )
        ORDER BY 
            ' || CASE 
                WHEN p_period = 'D' THEN 'TO_CHAR (pu.purchase_date, ''YYYY-MM-DD'')'
                WHEN p_period = 'W' THEN 'TO_CHAR (pu.purchase_date, ''IYYY"W"IW'')'
                WHEN p_period = 'M' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"M"MM'')'
                WHEN p_period = 'Q' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Q"Q'')'
                WHEN p_period = 'Y' THEN 'TO_CHAR (pu.purchase_date, ''YYYY"Y"'')'
            END || ',
            pu.customer_id';

    -- Open the ref cursor with the generated SQL
    OPEN p_results FOR v_sql;
END;
/

How to call it: #

DECLARE
    v_results SYS_REFCURSOR;
    v_period_label VARCHAR2(20);
    v_customer_id NUMBER;
    v_first_name VARCHAR2(50);
    v_last_name VARCHAR2(50);
    v_total_amt NUMBER;
BEGIN
    get_purchase_summary('W', v_results); -- Pass your period here (D/W/M/Q/Y)
    LOOP
        FETCH v_results INTO v_period_label, v_customer_id, v_first_name, v_last_name, v_total_amt;
        EXIT WHEN v_results%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_period_label || ' | ' || v_customer_id || ' | ' || v_first_name || ' ' || v_last_name || ' | ' || v_total_amt);
    END LOOP;
    CLOSE v_results;
END;
/

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