oracle按半小时统计,Oracle按年如何进行统计？
按照年统计view plaincopy to clipboardPRint?WITH tmp_table AS (SELECT MAX(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) -MIN(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) AS NUM,MIN(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) AS MINYEARF
按照年统计
view plaincopy to clipboardPRint?
WITH tmp_table AS (SELECT MAX(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) -
MIN(TO_CHAR(T。
PURRECEIVEDATE, ’yyyy’)) AS NUM,
MIN(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) AS MINYEAR
FROM LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECT TMP。
YEARSUM, TMP。YEAR, TMP。YMD
FROM (SELECT SUM(T。RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T。PURRECEIVEDATE, ’yyyy’) ORDER BY T。
PURRECEIVEDATE DESC) AS YEARSUM,
TO_CHAR(T。PURRECEIVEDATE, ’yyyy’) AS YEAR,
TO_CHAR(T。PURRECEIVEDATE, ’yyyy/mm/dd’) AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T。
PURRECEIVEDATE, ’yyyy’) ORDER BY T。PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
WHERE TMP。RN = 1
),
creatyear_table AS (
SELECT tmp_table。
MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table。NUM + 1
)
SELECT
ct。tmp_year,
NVL(st。
YEARSUM, 0) AS YEARSUM
FROM
creatyear_table ct,
select_table st
WHERE
ct。tmp_year = st。year(+)
WITH tmp_table AS (SELECT MAX(TO_CHAR(T。
PURRECEIVEDATE, ’yyyy’)) -
MIN(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) AS NUM,
MIN(TO_CHAR(T。PURRECEIVEDATE, ’yyyy’)) AS MINYEAR
FROM LB01_PURRECEIVEBOOK T
),
select_table AS (
SELECT TMP。
YEARSUM, TMP。YEAR, TMP。YMD
FROM (SELECT SUM(T。RECEIVEAMT) OVER(PARTITION BY TO_CHAR(T。PURRECEIVEDATE, ’yyyy’) ORDER BY T。
PURRECEIVEDATE DESC) AS YEARSUM,
TO_CHAR(T。PURRECEIVEDATE, ’yyyy’) AS YEAR,
TO_CHAR(T。PURRECEIVEDATE, ’yyyy/mm/dd’) AS YMD,
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(T。
PURRECEIVEDATE, ’yyyy’) ORDER BY T。PURRECEIVEDATE) AS RN
FROM LB01_PURRECEIVEBOOK T) TMP
WHERE TMP。RN = 1
),
creatyear_table AS (
SELECT tmp_table。
MINYEAR + LEVEL - 1 AS tmp_year FROM DUAL, tmp_table
CONNECT BY LEVEL <= tmp_table。NUM + 1
)
SELECT
ct。tmp_year,
NVL(st。
YEARSUM, 0) AS YEARSUM
FROM
creatyear_table ct,
select_table st
WHERE
ct。tmp_year = st。year(+)。
全部
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)