oracle笔记

1.按照时间分组数据 (比如每2小时,每4小时)

　　原理:把时间转化成数值,根据数值进行分组

　　代码([每2个小时]/分一组)

　　　　SELECT
            TRUNC (TO_NUMBER (TO_CHAR(TO_DATE(GCSJ,'yyyy-mm-dd hh24:mi:ss'),'hh24'))/2) AS GFSDKEY,
            COUNT(1) AS COUNT
        FROM
            HK_CLKK 
        WHERE TO_CHAR(TO_DATE(GCSJ,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')=TO_CHAR(SYSDATE,'yyyymmdd')
        GROUP BY TRUNC (TO_NUMBER (TO_CHAR(TO_DATE(GCSJ,'yyyy-mm-dd hh24:mi:ss'),'hh24'))/2)

2.过去七天数据分组

　　当过去七天都有数据的时候问题不大,但是,只有三天有数据,四天没数据,怎么补齐那四天才是关键

　　1.获取七天整个数据

SELECT
    TO_CHAR (SYSDATE - LEVEL + 1,'yyyy-mm-dd') AS DAY1
FROM
    DUAL CONNECT BY LEVEL <= 7

　　2.获取需要获取的数据

SELECT
                        SUBSTR (statrange, 0, 10) AS DAY1,
                        REGIONID AS REGIONID,
                        "SUM" (REGAMOUNT) AS COUNT,
                        REGIONNAME AS REGIONNAME
                    FROM
                        (
                            SELECT
                                *
                            FROM
                                REGION_AMOUNT_STAT
                            WHERE
                                DEVTYPE = #{devType }
                        )
                    GROUP BY
                        REGIONID,
                        REGIONNAME,
                        SUBSTR (statrange, 0, 10)

　　3.把上面的1和2连起来

SELECT 
 a.*,
 b.*
FROM
(
    SELECT
        TO_CHAR (
            SYSDATE - LEVEL + 1,
            'yyyy-mm-dd'
        ) AS DAY1
    FROM
        DUAL CONNECT BY LEVEL <= 7
) a
LEFT JOIN
(SELECT
    SUBSTR (statrange, 0, 10) AS DAY1,
    REGIONID AS REGIONID,
    "SUM" (REGAMOUNT) AS COUNT
FROM
    (
        SELECT
            *
        FROM
            REGION_AMOUNT_STAT
        WHERE
            DEVTYPE = '1'
    )
GROUP BY
    REGIONID,
    SUBSTR (statrange, 0, 10)
) b
ON a.day1 = b.day1

到此为止,就是该有的就都有了,再去分组计算起别名就好了

下面是完整的SQL代码

SELECT
    C.DAY1 AS DAY,
    C.REGIONID AS sbqID,
    C.REGIONNAME AS sbqName,
    NVL(C.COUNT,0) COUNT
    
FROM
    (
        SELECT
            b.DAY1 AS DAY1,
            TEST1.REGIONID AS REGIONID,
            test1.REGIONNAME AS REGIONNAME,
            test1. COUNT AS COUNT
        FROM
            (
                (
                    SELECT
                        TO_CHAR (
                            SYSDATE - LEVEL + 1,
                            'yyyy-mm-dd'
                        ) AS DAY1
                    FROM
                        DUAL CONNECT BY LEVEL <![CDATA[<= ]]> 7
                ) b
                LEFT JOIN (
                    SELECT
                        SUBSTR (statrange, 0, 10) AS DAY1,
                        REGIONID AS REGIONID,
                        "SUM" (REGAMOUNT) AS COUNT,
                        REGIONNAME AS REGIONNAME
                    FROM
                        (
                            SELECT
                                *
                            FROM
                                REGION_AMOUNT_STAT
                            WHERE
                                DEVTYPE = 'X'
                        )
                    GROUP BY
                        REGIONID,
                        REGIONNAME,
                        SUBSTR (statrange, 0, 10)
                ) test1 ON test1.DAY1 = b.DAY1
            )
    ) C
ORDER BY
    C.DAY1