大神们看看这个oracle 该怎么求和。

qq1002828 2024-01-28 09:16:42

 psn_no 是人员编码,adm_time开始时间,dscg_time是结束时间。

 

data_flag=1  代表第一次 ,data_flag= 2 是判断 间隔天数的标识:第一个结束 dscg_time 和下一个开始adm_time 在7天内就已经打上标识2 。 

要实现的效果:

1-2-2-2   这样是一个循环,假如有4笔,那么前面3笔的金额查询出来是0 ,第四笔是四笔金额的总和。

 

 

 

详细的数据如下所示:

 

aaaa_IDPSN_NODATA_FLAGADM_TIMEDSCG_TIMESETL_TIMEMEDFEE_SUMAMT
4059808174409000000010003621712023-01-05 00:00:002023-01-12 00:00:002023-01-12 09:47:3512291.08
4107371924409000000010003621722023-01-17 00:00:002023-01-19 00:00:002023-01-19 10:30:251850.25
4224820944409000000010003621722023-01-23 00:00:002023-02-14 00:00:002023-02-14 12:54:1052681.82
4253646844409000000010003621722023-02-17 00:00:002023-02-18 00:00:002023-02-19 15:14:212013.53
4356662044409000000010003621722023-02-24 00:00:002023-03-07 00:00:002023-03-07 15:30:2222769.61
4416765584409000000010003621722023-03-10 00:00:002023-03-16 00:00:002023-03-16 10:05:097769.22
4475029654409000000010003621722023-03-18 00:00:002023-03-24 00:00:002023-03-24 13:18:299812.16
4014338584409000000010010471612023-01-05 00:00:002023-01-05 00:00:002023-01-05 15:34:431033.45
4063568984409000000010010471622023-01-12 00:00:002023-01-12 00:00:002023-01-12 15:32:031508.66
4108734714409000000010010471622023-01-19 00:00:002023-01-19 00:00:002023-01-19 12:47:391088.82
4123345144409000000010010471622023-01-26 00:00:002023-01-26 00:00:002023-01-26 11:35:341398.27
4181431464409000000010010471612023-02-07 00:00:002023-02-07 00:00:002023-02-07 16:34:291542.95
4247946874409000000010010471622023-02-14 00:00:002023-02-18 00:00:002023-02-18 11:33:1621219.68
4323176064409000000010010471622023-02-23 00:00:002023-03-02 00:00:002023-03-02 15:14:2724504.8
4368976524409000000010010471622023-03-07 00:00:002023-03-09 00:00:002023-03-09 11:06:294167.75
4425942514409000000010010471622023-03-15 00:00:002023-03-17 00:00:002023-03-17 11:07:137234.52
4027706564409000000011013948612023-01-06 00:00:002023-01-07 00:00:002023-01-07 12:19:5414396.19
4070628114409000000011013948622023-01-13 00:00:002023-01-13 00:00:002023-01-13 13:24:151279.48
4112494844409000000011013948622023-01-19 00:00:002023-01-20 00:00:002023-01-20 10:10:5012382.39
4125943834409000000011013948622023-01-27 00:00:002023-01-27 00:00:002023-01-27 12:01:171348.63
4163893474409000000011013948622023-02-03 00:00:002023-02-04 00:00:002023-02-04 10:32:4715114.52
4198807564409000000011013948622023-02-10 00:00:002023-02-10 00:00:002023-02-10 13:35:101100.48
4003051214409000000310470359912022-12-28 00:00:002022-12-28 00:00:002023-01-04 10:15:385096.44
4052542444409000000310470359922023-01-04 00:00:002023-01-04 00:00:002023-01-11 09:51:501055.69
4057065624409000000310470359922023-01-11 00:00:002023-01-11 00:00:002023-01-11 16:39:011431.76
4102713474409000000310470359922023-01-18 00:00:002023-01-18 00:00:002023-01-18 14:15:031871.97
4121346834409000000310470359922023-01-25 00:00:002023-01-25 00:00:002023-01-25 12:26:371058.01
4154106914409000000310470359922023-02-01 00:00:002023-02-02 00:00:002023-02-02 11:55:346225.7
4190273084409000000310470359922023-02-08 00:00:002023-02-08 00:00:002023-02-09 09:48:311156.43
4277318154409000000310470359922023-02-15 00:00:002023-02-15 00:00:002023-02-23 10:57:085328.31
...全文
170 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
hqw791458916 2024-04-10
  • 打赏
  • 举报
回复 1

SELECT PSN_NO
, DATA_FLAG
, ADM_TIME
, DSCG_TIME
, MEDFEE_SUMAMT
--当序号为同组最大序号时返回和,否则为0
, DECODE(RNT,MAX_RNT,MEDFEE_S,0) AS MEDFEE_S
FROM (
SELECT PSN_NO
, DATA_FLAG
, ADM_TIME
, DSCG_TIME
, MEDFEE_SUMAMT
, RNT
--用当前行分组排序序号减当前行逐行累加之和 (每一组的结果是一样的,利用这个结果进行分组求和)
, SUM(MEDFEE_SUMAMT) OVER(PARTITION BY PSN_NO,RNT-SNT) AS MEDFEE_S
--同组中最大序号
, MAX(RNT) OVER(PARTITION BY PSN_NO,RNT-SNT) AS MAX_RNT
FROM (
SELECT PSN_NO
, DATA_FLAG
, ADM_TIME
, DSCG_TIME
, MEDFEE_SUMAMT
--ROW_NUMBER排序
, ROW_NUMBER() OVER(PARTITION BY PSN_NO ORDER BY ADM_TIME,DATA_FLAG) AS RNT
--分组逐行累加
, SUM(DATA_FLAG-1) OVER(PARTITION BY PSN_NO ORDER BY ADM_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SNT
FROM DW.HQW_TEST111
)
)

3,494

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧