【讨论】揪心的日期分析，求指点，求SQL!

显示全部楼层 · 2014-11-11 06:00:15

昨天有个日期分析让人揪心，弄了2个小时都没弄出来，求大神指点，具体如下：

有表shityou
-- Create table
create table SHITYOU
(
编号 NUMBER(38),
名称 VARCHAR2(10),
执行时间 TIMESTAMP(6),
随机数NUMBER(38)
)
tablespace CCBA_TABLESPACE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 1
minextents 1
maxextents unlimited
);复制代码示例数据如下：
编号名称执行时间随机数
3name106-11月-14 04.47.23.364000 下午1990
4name206-11月-14 05.01.47.000000 下午1470
5name306-11月-14 05.16.11.000000 下午7698
6name406-11月-14 05.30.35.000000 下午5104
7name506-11月-14 05.44.59.000000 下午9855
8name606-11月-14 05.59.23.000000 下午3203
9name706-11月-14 06.13.47.000000 下午3917
10name806-11月-14 06.28.11.000000 下午4915
11name906-11月-14 06.42.35.000000 下午6161
12name1006-11月-14 06.56.59.000000 下午904
13name1106-11月-14 07.11.23.000000 下午7443
14name1206-11月-14 07.25.47.000000 下午4027
15name1306-11月-14 07.40.11.000000 下午5965
16name1406-11月-14 07.54.35.000000 下午3452
17name1506-11月-14 08.08.59.000000 下午1713
18name1606-11月-14 08.23.23.000000 下午1311
19name1706-11月-14 08.37.47.000000 下午1188
20name1806-11月-14 08.52.11.000000 下午7863
21name1906-11月-14 09.06.35.000000 下午7381
22name2006-11月-14 09.20.59.000000 下午8352
23name2106-11月-14 09.35.23.000000 下午6799
24name2206-11月-14 09.49.47.000000 下午3266
25name2306-11月-14 10.04.11.000000 下午8601
26name2406-11月-14 10.18.35.000000 下午2593
27name2506-11月-14 10.32.59.000000 下午2650
28name2606-11月-14 10.47.23.000000 下午9831
29name2706-11月-14 11.01.47.000000 下午6303
30name2806-11月-14 11.16.11.000000 下午9685
31name2906-11月-14 11.30.35.000000 下午1122
32name3006-11月-14 11.44.59.000000 下午6952
33name3106-11月-14 11.59.23.000000 下午3368
34name3207-11月-14 12.13.47.000000 上午5208
35name3307-11月-14 12.28.11.000000 上午2024
36name3407-11月-14 12.42.35.000000 上午225
37name3507-11月-14 12.56.59.000000 上午876
38name3607-11月-14 01.11.23.000000 上午9658
39name3707-11月-14 01.25.47.000000 上午6730
40name3807-11月-14 01.40.11.000000 上午4734
41name3907-11月-14 01.54.35.000000 上午8179
42name4007-11月-14 02.08.59.000000 上午7330
43name4107-11月-14 02.23.23.000000 上午8864
44name4207-11月-14 02.37.47.000000 上午4147
45name4307-11月-14 02.52.11.000000 上午8712
46name4407-11月-14 03.06.35.000000 上午7411
47name4507-11月-14 03.20.59.000000 上午956
48name4607-11月-14 03.35.23.000000 上午909
49name4707-11月-14 03.49.47.000000 上午941
50name4807-11月-14 04.04.11.000000 上午7971
51name4907-11月-14 04.18.35.000000 上午3388
52name5007-11月-14 04.32.59.000000 上午2660
53name5107-11月-14 04.47.23.000000 上午2888
54name5207-11月-14 05.01.47.000000 上午5119
55name5307-11月-14 05.16.11.000000 上午2758
56name5407-11月-14 05.30.35.000000 上午9702
57name5507-11月-14 05.44.59.000000 上午3347
58name5607-11月-14 05.59.23.000000 上午4640
59name5707-11月-14 06.13.47.000000 上午1302
60name5807-11月-14 06.28.11.000000 上午4001
61name5907-11月-14 06.42.35.000000 上午4789
62name6007-11月-14 06.56.59.000000 上午977
63name6107-11月-14 07.11.23.000000 上午155
64name6207-11月-14 07.25.47.000000 上午7286
65name6307-11月-14 07.40.11.000000 上午124
66name6407-11月-14 07.54.35.000000 上午8185
67name6507-11月-14 08.08.59.000000 上午1129
68name6607-11月-14 08.23.23.000000 上午8319
69name6707-11月-14 08.37.47.000000 上午3686
70name6807-11月-14 08.52.11.000000 上午2696
71name6907-11月-14 09.06.35.000000 上午8187
72name7007-11月-14 09.20.59.000000 上午9893
73name7107-11月-14 09.35.23.000000 上午8642
74name7207-11月-14 09.49.47.000000 上午52
复制代码插入数据的语句如下：
--循环插入数据
DECLARE
v_cnt NUMBER(38):=1;
v_startdate TIMESTAMP WITH TIME ZONE;
BEGIN
v_startdate:=SYSTIMESTAMP;

LOOP

EXIT WHEN v_cnt > 100000;

INSERT INTO shityou(编号,名称,执行时间,随机数)

VALUES(seq_shityou.NEXTVAL,'name'||v_cnt,v_startdate,TRUNC(dbms_random.value(0,10000)));

v_startdate:=v_startdate+0.01;

v_cnt:=v_cnt+1;
END LOOP;
COMMIT;
dbms_output.put_line('run ok!');
END;复制代码
需求：

查询下一个月每天执行最晚的时间(注意：需排除每天08:00~18:00的执行时间,当天18:00~第二天08:00算到当天，第二月第一天08:00前算作本月,本月第一天09:00前不计入本月执行时间,并且第二天08:00比当天23:59:59秒大)
示例：

假如存在如下数据，在计算12月8日的数据时，判断第二天08:00前是否有数据，如果有数据则取最大数据作为12月8日执行最晚时间；如果不存在第二天08:00前的数据，则已当天最晚时间作为最晚执行时间；下面12月8日的最晚执行时间为：3266name326409-12月-14 07.54.35.000000 上午5181

3188name318608-12月-14 01.11.23.000000 下午17
3189name318708-12月-14 01.25.47.000000 下午9212
3190name318808-12月-14 01.40.11.000000 下午6042
3191name318908-12月-14 01.54.35.000000 下午3730
3192name319008-12月-14 02.08.59.000000 下午7526
3193name319108-12月-14 02.23.23.000000 下午4660
3194name319208-12月-14 02.37.47.000000 下午2048
3195name319308-12月-14 02.52.11.000000 下午41
3196name319408-12月-14 03.06.35.000000 下午7049
3197name319508-12月-14 03.20.59.000000 下午3155
3198name319608-12月-14 03.35.23.000000 下午6748
3199name319708-12月-14 03.49.47.000000 下午1441
3200name319808-12月-14 04.04.11.000000 下午5902
3201name319908-12月-14 04.18.35.000000 下午8096
3202name320008-12月-14 04.32.59.000000 下午9705
3203name320108-12月-14 04.47.23.000000 下午8573
3204name320208-12月-14 05.01.47.000000 下午3529
3205name320308-12月-14 05.16.11.000000 下午8825
3206name320408-12月-14 05.30.35.000000 下午1945
3207name320508-12月-14 05.44.59.000000 下午3880
3208name320608-12月-14 05.59.23.000000 下午3521
3209name320708-12月-14 06.13.47.000000 下午5762
3210name320808-12月-14 06.28.11.000000 下午7748
3211name320908-12月-14 06.42.35.000000 下午4035
3212name321008-12月-14 06.56.59.000000 下午1194
3213name321108-12月-14 07.11.23.000000 下午1181
3214name321208-12月-14 07.25.47.000000 下午2497
3215name321308-12月-14 07.40.11.000000 下午1523
3216name321408-12月-14 07.54.35.000000 下午4019
3217name321508-12月-14 08.08.59.000000 下午6308
3218name321608-12月-14 08.23.23.000000 下午6102
3219name321708-12月-14 08.37.47.000000 下午7525
3220name321808-12月-14 08.52.11.000000 下午3271
3221name321908-12月-14 09.06.35.000000 下午1534
3222name322008-12月-14 09.20.59.000000 下午3654
3223name322108-12月-14 09.35.23.000000 下午9525
3224name322208-12月-14 09.49.47.000000 下午6129
3225name322308-12月-14 10.04.11.000000 下午645
3226name322408-12月-14 10.18.35.000000 下午4591
3227name322508-12月-14 10.32.59.000000 下午3706
3228name322608-12月-14 10.47.23.000000 下午7978
3229name322708-12月-14 11.01.47.000000 下午2832
3230name322808-12月-14 11.16.11.000000 下午6524
3231name322908-12月-14 11.30.35.000000 下午55
3232name323008-12月-14 11.44.59.000000 下午4742
3233name323108-12月-14 11.59.23.000000 下午7453
3234name323209-12月-14 12.13.47.000000 上午7375
3235name323309-12月-14 12.28.11.000000 上午1145
3236name323409-12月-14 12.42.35.000000 上午3065
3237name323509-12月-14 12.56.59.000000 上午4028
3238name323609-12月-14 01.11.23.000000 上午9714
3239name323709-12月-14 01.25.47.000000 上午9887
3240name323809-12月-14 01.40.11.000000 上午4518
3241name323909-12月-14 01.54.35.000000 上午7466
3242name324009-12月-14 02.08.59.000000 上午8694
3243name324109-12月-14 02.23.23.000000 上午1212
3244name324209-12月-14 02.37.47.000000 上午394
3245name324309-12月-14 02.52.11.000000 上午8540
3246name324409-12月-14 03.06.35.000000 上午5254
3247name324509-12月-14 03.20.59.000000 上午1546
3248name324609-12月-14 03.35.23.000000 上午969
3249name324709-12月-14 03.49.47.000000 上午8151
3250name324809-12月-14 04.04.11.000000 上午7566
3251name324909-12月-14 04.18.35.000000 上午320
3252name325009-12月-14 04.32.59.000000 上午4689
3253name325109-12月-14 04.47.23.000000 上午402
3254name325209-12月-14 05.01.47.000000 上午966
3255name325309-12月-14 05.16.11.000000 上午2031
3256name325409-12月-14 05.30.35.000000 上午8742
3257name325509-12月-14 05.44.59.000000 上午4297
3258name325609-12月-14 05.59.23.000000 上午2280
3259name325709-12月-14 06.13.47.000000 上午1508
3260name325809-12月-14 06.28.11.000000 上午1657
3261name325909-12月-14 06.42.35.000000 上午8470
3262name326009-12月-14 06.56.59.000000 上午3936
3263name326109-12月-14 07.11.23.000000 上午8264
3264name326209-12月-14 07.25.47.000000 上午3216
3265name326309-12月-14 07.40.11.000000 上午5124
3266name326409-12月-14 07.54.35.000000 上午5181
3267name326509-12月-14 08.08.59.000000 上午4259
3268name326609-12月-14 08.23.23.000000 上午5210
3269name326709-12月-14 08.37.47.000000 上午2211
3270name326809-12月-14 08.52.11.000000 上午1411
3271name326909-12月-14 09.06.35.000000 上午8538
3272name327009-12月-14 09.20.59.000000 上午3775
3273name327109-12月-14 09.35.23.000000 上午4797
3274name327209-12月-14 09.49.47.000000 上午8737
3275name327309-12月-14 10.04.11.000000 上午3666
3276name327409-12月-14 10.18.35.000000 上午74
3277name327509-12月-14 10.32.59.000000 上午8909
3278name327609-12月-14 10.47.23.000000 上午1071
3279name327709-12月-14 11.01.47.000000 上午7098
3280name327809-12月-14 11.16.11.000000 上午8797
3281name327909-12月-14 11.30.35.000000 上午2158
3282name328009-12月-14 11.44.59.000000 上午4027
3283name328109-12月-14 11.59.23.000000 上午2668
3284name328209-12月-14 12.13.47.000000 下午3235
3285name328309-12月-14 12.28.11.000000 下午7490
3286name328409-12月-14 12.42.35.000000 下午2087
3287name328509-12月-14 12.56.59.000000 下午3484
3288name328609-12月-14 01.11.23.000000 下午1751
3289name328709-12月-14 01.25.47.000000 下午9655
3290name328809-12月-14 01.40.11.000000 下午2654
3291name328909-12月-14 01.54.35.000000 下午4722
3292name329009-12月-14 02.08.59.000000 下午2499
3293name329109-12月-14 02.23.23.000000 下午3650
3294name329209-12月-14 02.37.47.000000 下午8152
3295name329309-12月-14 02.52.11.000000 下午7735
3296name329409-12月-14 03.06.35.000000 下午2590
3297name329509-12月-14 03.20.59.000000 下午3876
3298name329609-12月-14 03.35.23.000000 下午5471
3299name329709-12月-14 03.49.47.000000 下午5605
3300name329809-12月-14 04.04.11.000000 下午751
3301name329909-12月-14 04.18.35.000000 下午6666
3302name330009-12月-14 04.32.59.000000 下午84
3303name330109-12月-14 04.47.23.000000 下午4745
3304name330209-12月-14 05.01.47.000000 下午2948
3305name330309-12月-14 05.16.11.000000 下午6304
3306name330409-12月-14 05.30.35.000000 下午3986
3307name330509-12月-14 05.44.59.000000 下午4764
3308name330609-12月-14 05.59.23.000000 下午9312
3309name330709-12月-14 06.13.47.000000 下午2038
3310name330809-12月-14 06.28.11.000000 下午9130
3311name330909-12月-14 06.42.35.000000 下午7539
3312name331009-12月-14 06.56.59.000000 下午6455
3313name331109-12月-14 07.11.23.000000 下午6307
3314name331209-12月-14 07.25.47.000000 下午4177
3315name331309-12月-14 07.40.11.000000 下午4892
3316name331409-12月-14 07.54.35.000000 下午5961
3317name331509-12月-14 08.08.59.000000 下午6023
3318name331609-12月-14 08.23.23.000000 下午1748
3319name331709-12月-14 08.37.47.000000 下午4967
3320name331809-12月-14 08.52.11.000000 下午4780
3321name331909-12月-14 09.06.35.000000 下午7835
3322name332009-12月-14 09.20.59.000000 下午8982
3323name332109-12月-14 09.35.23.000000 下午7709
3324name332209-12月-14 09.49.47.000000 下午5861
3325name332309-12月-14 10.04.11.000000 下午9563
3326name332409-12月-14 10.18.35.000000 下午6273
3327name332509-12月-14 10.32.59.000000 下午6366
3328name332609-12月-14 10.47.23.000000 下午8673
3329name332709-12月-14 11.01.47.000000 下午4209
3330name332809-12月-14 11.16.11.000000 下午9271
3331name332909-12月-14 11.30.35.000000 下午825
3332name333009-12月-14 11.44.59.000000 下午5575
3333name333109-12月-14 11.59.23.000000 下午1156
复制代码

各位亲们，求分析，求指点，求SQL!!!!

千问 · 2014-11-11 06:00:15

亲们，求SQL！！！

千问 · 2014-11-11 06:00:15

只需给出少量数据，还要给出预期结果！
SELECT * FROM (
select T.*
,CAST(执行时间 AS DATE)-8/24 REPORT_DATE
,ROW_NUMBER() OVER(PARTITION BY CAST(执行时间 AS DATE)-8/24 ORDER BY 执行时间 DESC) RN
FROM SHITYOU
)
WHERE RN=1;

千问 · 2014-11-11 06:00:15

newkid 发表于 2014-11-7 10:42
只需给出少量数据，还要给出预期结果！
SELECT * FROM (
select T.*
哥们，很感谢你的关注
比如说现在需要统计12月的数据，那么需要提取12月每一天的最大执行时间，并且是不重复的，当天18:00~23:59:59+第二天00:00:00~08:00:00算当天的，一次类推，每天只允许出现一条数据，并且是最晚的数据，比如，12月7日最晚执行数据=(如果 8日00:00~08:00有执行数据则取8日08:00前最晚数据作为7日最晚执行数据；如果 8日00:00~08:00无执行数据，则统计7日18:00~23:59:59期间最晚的时间)

千问 · 2014-11-11 06:00:15

执行时间-8/24根据这个分月

千问 · 2014-11-11 06:00:15

select c.*
from （select max(a.new_time) as time, to_char(a.new_time, 'yyyymmdd')
from (select 执行时间 - NUMTODSINTERVAL(8, 'hour') as new_time

from shityou) a
group by to_char(a.new_time, 'yyyymmdd') order by to_char(a.new_time, 'yyyymmdd')）b, shityou c
where b.time + NUMTODSINTERVAL(8, 'hour') = c.执行时间;

千问 · 2014-11-11 06:00:15

moseslin 发表于 2014-11-7 13:14
执行时间-8/24根据这个分月
是的，大体的时间段是如此，但是需要统计每天最晚执行的一条数据

千问 · 2014-11-11 06:00:15

iyee_tu 发表于 2014-11-7 14:23
select c.*
from （select max(a.new_time) as time, to_char(a.new_time, 'yyyymmdd')
from (select ...
看上去想那么回事儿，我测测

千问 · 2014-11-11 06:00:15

测试数据确实太多了

千问 · 2014-11-11 06:00:15

jinaqu 发表于 2014-11-7 13:09
哥们，很感谢你的关注
比如说现在需要统计12月的数据，那么需要提取12月每一天的最大执行时间，并且是 ...
给出一个简洁的能说明问题的例子。预期结果是如何的，我的SQL结果又是如何的。