OVER(PARTITION BY OVER (ORDER BY_sbbxx-优快云博客

本文深入探讨SQL中使用排序和聚合操作的技巧，通过具体示例展示如何获取排序后的数据记录并进行特定数据的查询。同时介绍了如何计算时间段内的统计指标，并计算比例和平均值。内容覆盖了SQL的基本用法及复杂查询策略。

--over 排序后记录对排序后的数据，给出相应的记录信息，然后取排序后的某条数据就简单了
SELECT * FROM t_drp_zd_bb_sbb_bak A ORDER BY A.SBBXXRQ DESC;
SELECT row_number() OVER (ORDER BY A.Sbbxxrq DESC),A.SBBXXID FROM t_drp_zd_bb_sbb_bak A;
SELECT row_number() OVER (ORDER BY A.Sbbxxrq DESC) RE,A.SBBXXID FROM t_drp_zd_bb_sbb_bak A;

SELECT * FROM (
SELECT row_number() OVER (ORDER BY A.Sbbxxrq DESC) RE,A.SBBXXID FROM t_drp_zd_bb_sbb_bak A
) A WHERE A.SBBXXID='fbc769652c8841d4bf58f386af298a1a' AND re=1;
--相当于
SELECT * FROM (
SELECT * FROM (
SELECT * FROM t_drp_zd_bb_sbb_bak A ORDER BY A.SBBXXRQ DESC
) A WHERE ROWNUM =1
) A WHERE A.Sbbxxid='fbc769652c8841d4bf58f386af298a1a'
;
--当语句有order by 的时候，以外层的为准

---按时段算和，算比例时是这个这段的比上查询的总数
SELECT A.WLDWXIID MDID,
       A.WLDWBH MDBH,
       A.WLDWMC MDMC,
       TO_CHAR(A.XSRQ, 'YYYY-MM-DD') XSRQ,
       TO_CHAR(A.XSRQ, 'hh24') || ':00:00 - ' || TO_CHAR(A.XSRQ, 'hh24') ||
       ':59:59' SJ,
       TO_CHAR(A.XSRQ, 'day') WEEK,
       A.JYBS JYBS,
       SUM(A.JYBS) OVER(PARTITION BY NULL) JYBS_Z,
       TO_CHAR_FROM_NUMBER(ROUND(A.JYBS /
                                 DECODE(SUM(A.JYBS) OVER(PARTITION BY NULL),
                                        0,
                                        1,
                                        SUM(A.JYBS) OVER(PARTITION BY NULL)),
                                 4) * 100) || '%' JYBS_BL,
       A.YSJE JE,
       SUM(A.YSJE) OVER(PARTITION BY NULL) YSJE_Z,
       TO_CHAR_FROM_NUMBER(ROUND(A.YSJE /
                                 DECODE(SUM(A.YSJE) OVER(PARTITION BY NULL),
                                        0,
                                        1,
                                        NULL,
                                        1,
                                        SUM(A.YSJE) OVER(PARTITION BY NULL)),
                                 4) * 100) || '%' JE_BL,
       ROUND(A.YSJE / DECODE(A.JYBS, 0, 1, A.JYBS), 2) PJJE
FROM (SELECT A.WLDWXIID,
               A.WLDWBH,
               A.WLDWMC,
               TO_DATE(TO_CHAR(A.XSRQ, 'YYYY-MM-DD hh24'), 'YYYY-MM-DD hh24') XSRQ,
               SUM(A.SSJE) YSJE,
               COUNT(A.POSID) JYBS
          FROM T_DRP_XS_POS A
         WHERE A.DELFLAG = 0
           AND A.XSRQ >= TO_DATE('2015-05-04', 'yyyy-mm-dd')
           AND A.XSRQ < TO_DATE('2015-05-04', 'yyyy-mm-dd') + 1
         GROUP BY A.WLDWXIID,
                  A.WLDWBH,
                  A.WLDWMC,
                  TO_CHAR(A.XSRQ, 'YYYY-MM-DD hh24')) A
ORDER BY A.WLDWXIID, TO_CHAR(A.XSRQ, 'YYYY-MM-DD hh24') ASC