Combining grouping set queries into one-CodePudding

I have 5 different queries that work fine but basically do the same thing. The difference is that they group by different periods.

My question is can these 5 queries be combined into 1 query perhaps a procedure, Where I pass in a D (Day), W (Week) M (Month), Q (quarter) or Y (year).

Below are the queries and some test data. Thanks in advance to all who respond.


 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;

/* purchases per day for each customer */ 

SELECT    TO_CHAR (p.purchase_date, 'YYYY-MM-DD')   AS year_mon_day
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'YYYY-MM-DD'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'YYYY-MM-DD'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'YYYY-MM-DD'), p.customer_id;

/* purchases per week for each customer */ 

SELECT    TO_CHAR (p.purchase_date, 'IYYY"W"IW')    AS year_week
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'IYYY"W"IW'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'IYYY"W"IW'), p.customer_id;

/* purchases per month for each customer */ 

SELECT    TO_CHAR (p.purchase_date, 'YYYY"M"MM')    AS year_month
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'YYYY"M"MM'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'YYYY"M"MM'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'YYYY"M"MM'), p.customer_id;

/* purchases per quarter for each customer */ 

SELECT    TO_CHAR (p.purchase_date, 'YYYY"Q"Q') AS year_quarter
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'YYYY"Q"Q'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'YYYY"Q"Q'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'YYYY"Q"Q'), p.customer_id;

/* purchases per year for each customer */ 

SELECT    TO_CHAR (p.purchase_date, 'YYYY"Y"')  AS year
,     p.customer_id
,     c.first_name
,     c.last_name
,     SUM (p.quantity * i.price)        AS total_amt
FROM      purchases  p
JOIN      customers  c  ON  p.customer_id = c.customer_id
JOIN      items      i  ON  p.product_id  = i.product_id
GROUP BY  GROUPING SETS (   (TO_CHAR (p.purchase_date, 'YYYY"Y"'), p.customer_id, c.first_name, c.last_name)
      , (TO_CHAR (p.purchase_date, 'YYYY"Y"'))
, ()
)
ORDER BY  TO_CHAR (p.purchase_date, 'YYYY"Y"'), p.customer_id;

CodePudding user response：

If you want it as a procedure then:

CREATE PROCEDURE get_customer_data(
  i_period IN  VARCHAR2,
  o_cursor OUT SYS_REFCURSOR
)
AS
  v_format VARCHAR2(10);
BEGIN
  v_format := CASE UPPER(i_period)
              WHEN 'D' THEN 'YYYY-MM-DD'
              WHEN 'W' THEN 'IYYY"W"IW'
              WHEN 'M' THEN 'YYYY"M"MM'
              WHEN 'Q' THEN 'YYYY"Q"Q'
              WHEN 'Y' THEN 'YYYY"Y"'
              ELSE          'YYYY-MM-DD'
              END;

  OPEN o_cursor FOR
    SELECT TO_CHAR (p.purchase_date, v_format) AS period
    ,      p.customer_id
    ,      c.first_name
    ,      c.last_name
    ,      SUM (p.quantity * i.price) AS total_amt
    FROM   purchases  p
    JOIN   customers  c  ON  p.customer_id = c.customer_id
    JOIN   items      i  ON  p.product_id  = i.product_id
    GROUP BY
      GROUPING SETS(
        ( TO_CHAR (p.purchase_date, v_format), p.customer_id, c.first_name, c.last_name )
      , TO_CHAR (p.purchase_date, v_format)
      , ()
      )
    ORDER BY TO_CHAR (p.purchase_date, v_format), p.customer_id;
END;
/

fiddle

CodePudding user response：

You can certainly do this in a procedure or function, either REF CURSOR return like MT0's answer, (if you can handle how to interface with that) or returning a nested table object, etc. But involving functions does add more complication and may not be ideal for simple needs. It may be simpler just to write a view with a set of UNION ALLs and use a literal to select the one you want.

CREATE OR REPLACE myview AS
SELECT 'D' period,
       [column_list]
  FROM [table list with joins]
 GROUP BY TO_CHAR(purchase_date,'YYYY-MM-DD'),customer_id,first_name,last_name
UNION ALL
SELECT 'M' period,
       [column_list]
  FROM [table list with joins]
 GROUP BY TO_CHAR(purchase_date,'YYYY-MM'),customer_id,first_name,last_name
UNION ALL
SELECT 'Y' period,
       [column_list]
  FROM [table list with joins]
 GROUP BY TO_CHAR(purchase_date,'YYYY'),customer_id,first_name,last_name
[etc...]

Then query it:

SELECT * FROM myview WHERE period = 'D'

Oracle should skip the work behind the other query blocks in the UNION ALL that don't match the literal period requested in your predicate, so there's no performance penalty.