论坛上的一个问题（做个记录）

最新推荐文章于 2023-04-01 19:32:16 发布

congcai5255

最新推荐文章于 2023-04-01 19:32:16 发布

阅读量71

点赞数

一个订单表,三个字段. 订单号,完成时间,到货时间.
需要统计每个月份,完成订单数量, 完成订单中到货数量,但是要累计.
例如:
2014-01 完成时间在2014-01 的订单数量,  完成时间在2014-01,且到货时间在2014-01的订单数量
2014-02 完成时间在2014-01,02 的订单数量, 完成时间在2014-01,02,且到货时间在2014-01,02的订单数量
2014-03 完成时间在2014-01,02,03 的订单数量, 完成时间在2014-01,02,03且到货时间在2014-01,02,03的订单数量

下面给出建表的sql,方便大家测试
  create TABLE TEMPA as
  (
   SELECT 'NO001' ORDER_NO, TO_DATE('2014-01-02','yyyy-MM-dd')  COMPLETE_DATE , TO_DATE('2014-01-02','yyyy-MM-dd') DDATE FROM DUAL
   UNION ALL
   SELECT 'NO002' ORDER_NO, TO_DATE('2014-01-03','yyyy-MM-dd')  COMPLETE_DATE , TO_DATE('2014-02-02','yyyy-MM-dd') DDATE FROM DUAL
   UNION ALL
   SELECT 'NO003' ORDER_NO, TO_DATE('2014-02-02','yyyy-MM-dd')  COMPLETE_DATE , TO_DATE('2014-02-02','yyyy-MM-dd') DDATE FROM DUAL
   UNION ALL
   SELECT 'NO004' ORDER_NO, TO_DATE('2014-02-02','yyyy-MM-dd')  COMPLETE_DATE , TO_DATE('2014-03-02','yyyy-MM-dd') DDATE FROM DUAL
   UNION ALL
   SELECT 'NO005' ORDER_NO, TO_DATE('2014-03-01','yyyy-MM-dd')  COMPLETE_DATE , TO_DATE('2014-04-02','yyyy-MM-dd') DDATE FROM DUAL
   UNION ALL
   SELECT 'NO006' ORDER_NO, TO_DATE('2014-03-05','yyyy-MM-dd')  COMPLETE_DATE , NULL DDATE FROM DUAL
   UNION ALL
   SELECT 'NO007' ORDER_NO, TO_DATE('2014-01-02','yyyy-MM-dd')  COMPLETE_DATE , TO_DATE('2014-02-02','yyyy-MM-dd') DDATE FROM DUAL
  )
;

需要的效果图如下
订单.png

(最好是一条sql统计出来, 目前是用的每个月1条sql,用了12个拼起来的. 查询不会跨年)

三个解决方案：
A:
SELECT COMPLETE_MONTH,
SUM(COMPLETE_CNT) OVER(ORDER BY COMPLETE_MONTH),
SUM(NVL(DCNT, 0)) OVER(ORDER BY COMPLETE_MONTH)
FROM (SELECT EXTRACT(MONTH FROM COMPLETE_DATE) AS COMPLETE_MONTH,
COUNT(*) AS COMPLETE_CNT
FROM TEMPA
GROUP BY EXTRACT(MONTH FROM COMPLETE_DATE)) A
LEFT OUTER JOIN (SELECT EXTRACT(MONTH FROM DDATE) AS DMONTH,
COUNT(*) AS DCNT
FROM TEMPA
GROUP BY EXTRACT(MONTH FROM DDATE)) B
ON (A.COMPLETE_MONTH = B.DMONTH);

B:
SELECT COMPLETE_MONTH,
SUM(COMPLETE_CNT) OVER(ORDER BY COMPLETE_MONTH),
SUM(D_CNT) OVER(ORDER BY COMPLETE_MONTH)
FROM (SELECT COMPLETE_MONTH,
MAX(COMPLETE_CNT) COMPLETE_CNT,
MAX(DCNT) D_CNT
FROM (SELECT EXTRACT(MONTH FROM COMPLETE_DATE) AS COMPLETE_MONTH,
EXTRACT(MONTH FROM DDATE) AS D_MONTH,
COUNT(*) OVER(PARTITION BY EXTRACT(MONTH FROM COMPLETE_DATE)) AS COMPLETE_CNT,
COUNT(*) OVER(PARTITION BY EXTRACT(MONTH FROM DDATE)) AS DCNT
FROM TEMPA)
WHERE COMPLETE_MONTH >= NVL(D_MONTH, 0)
GROUP BY COMPLETE_MONTH);

LEE:
WITH Q1 AS
(SELECT TRUNC(COMPLETE_DATE, 'MM') AS CDAT, TRUNC(DDATE, 'MM') AS DDAT
FROM TEMPA),
Q2 AS
(SELECT DAT, SUM(C_CNT) AS C_CNT, SUM(D_CNT) AS D_CNT
FROM (SELECT CDAT AS DAT, COUNT(CDAT) AS C_CNT, 0 AS D_CNT FROM Q1
GROUP BY CDAT
UNION ALL
SELECT DDAT AS DAT, 0 AS C_CNT, COUNT(DDAT) AS D_CNT FROM Q1
GROUP BY DDAT)
GROUP BY DAT)

SELECT DAT,
SUM(C_CNT) OVER(ORDER BY DAT) AS C_CNT,
SUM(D_CNT) OVER(ORDER BY DAT) AS D_CNT
FROM Q2
WHERE C_CNT <> 0;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26344835/viewspace-1354888/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26344835/viewspace-1354888/