oracle 将分组集查询合并为一个查询

9vw9lbht 于 2023-01-20 发布在 Oracle

关注(0)|答案(2)|浏览(139)

我有5个不同的查询，工作正常，但基本上做同样的事情。不同的是，他们按不同的时期分组。
我的问题是这5个查询是否可以合并为1个查询（可能是一个过程），其中我传递了D（天）、W（周）、M（月）、Q（季度）或Y（年）。
以下是一些疑问和测试数据。提前感谢所有回复的人。

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;

oracle

来源：https://stackoverflow.com/questions/75152313/combining-grouping-set-queries-into-one

2条答案

按热度按时间

v6ylcynt1#

你当然可以在一个过程或函数中做到这一点，或者像MT0的答案一样返回REF CURSOR，（如果你能处理如何与之接口的话），或者返回一个嵌套表对象，等等。但是涉及函数确实增加了更多的复杂性，并且可能不适合简单的需求。仅仅用一组UNION ALL写一个视图，然后使用一个文字来选择你想要的视图，可能会更简单。

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...]

然后查询：

SELECT * FROM myview WHERE period = 'D'

Oracle应该跳过UNION ALL中与 predicate 中请求的文本句点不匹配的其他查询块后面的工作，因此不会影响性能。

赞(0）回复(0）举报 2023-01-20

avwztpqn2#

如果您希望将其作为一个程序，则：

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

赞(0）回复(0）举报 2023-01-20

我来回答

oracle 将分组集查询合并为一个查询

2条答案

相关问题

热门标签

最新问答