按状态分组的历史记录表中的SQL最小/最大日期

Question

此查询用于显示我要使用的数据：

SELECT
nenh_entity
,nenh_trans_date
,nenh_trans_time
,nenh_trans_date*10000000+nenh_trans_time
,nenh_new_status_6
FROM
ntcenh
;
nenh_entity   nenh_trans_date  nenh_trans_time  (expression)           nenh_new_status_6
------------------------------------------------------------------------------------------
EDNSB002      20180808         201230           201808080201230        SCHED DT           
EDNSB002      20180723         104731           201807230104731        STANDBY            
EDNSB002      20180723         101747           201807230101747        STANDBY            
EDNSB002      20180723         83237            201807230083237        STANDBY            
EDNSB002      20180723         80252            201807230080252        STANDBY            
EDNSB002      20180723         62250            201807230062250        STANDBY            
EDNSB002      20180723         55311            201807230055311        STANDBY            
EDNSB002      20180723         54329            201807230054329        STANDBY            
EDNSB002      20180723         51354            201807230051354        STANDBY            
EDNSB002      20180723         44208            201807230044208        PRODUCTIVE         
EDNSB002      20180723         42728            201807230042728        PRODUCTIVE         
EDNSB002      20180723         40551            201807230040551        PRODUCTIVE         
EDNSB002      20180723         35112            201807230035112        PRODUCTIVE         
EDNSB002      20180723         31912            201807230031912        PRODUCTIVE         
EDNSB002      20180723         31138            201807230031138        STANDBY            
EDNSB002      20180723         23411            201807230023411        STANDBY            
EDNSB002      20180723         21546            201807230021546        STANDBY            
EDNSB002      20180723         13707            201807230013707        PRODUCTIVE         
EDNSB002      20180723         11322            201807230011322        PRODUCTIVE         
EDNSB002      20180723         5414             201807230005414        PRODUCTIVE         

我希望SQL生成的是：

nenh_new_status_6  (min)                  (max)                  
-----------------------------------------------------------------
SCHED DT           201808080201230        201808080201230
STANDBY            201807230051354        201807230104731
PRODUCTIVE         201807230031912        201807230044208
STANDBY            201807230021546        201807230031138
PRODUCTIVE         201807230005414        201807230013707

我不知道该怎么做。我整个上午都在努力研究。终于决定在这里发帖了。谢谢Kent

Answer 1

select nenh_new_status_6,
min(nenh_trans_date*10000000+nenh_trans_time), 
max(nenh_trans_date*10000000+nenh_trans_time)
from ntcenh
group by nenh_new_status_6

Answer 2

如果您的Informix版本足够新，您可以尝试使用以下内容，利用OLAP窗口表达式。

-- The setup
CREATE TABLE ntcenh
(
nenh_entity       CHAR(10)
, nenh_trans_date   INTEGER
, nenh_trans_time   INTEGER
, nenh_new_status_6 CHAR(10)
);
INSERT INTO ntcenh VALUES ('EDNSB002', 20180808, 201230, 'SCHED DT');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723, 104731, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723, 101747, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  83237, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  80252, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  62250, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  55311, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  54329, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  51354, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  44208, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  42728, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  40551, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  35112, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  31912, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  31138, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  23411, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  21546, 'STANDBY');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  13707, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,  11322, 'PRODUCTIVE');
INSERT INTO ntcenh VALUES ('EDNSB002', 20180723,   5414, 'PRODUCTIVE');

查询：

SELECT DISTINCT
vt2.nenh_new_status_6
, MIN(vt2.nenh_datetime) 
OVER(PARTITION BY vt2.status_group) AS min_nenh_datetime
, MAX(vt2.nenh_datetime) 
OVER(PARTITION BY vt2.status_group) AS max_nenh_datetime
FROM
(
SELECT
vt1.nenh_new_status_6
, vt1.nenh_datetime
, SUM(vt1.changed) OVER (ORDER BY vt1.nenh_datetime DESC) AS status_group
FROM
(
SELECT
nenh_new_status_6
, nenh_trans_date * 10000000 + nenh_trans_time AS nenh_datetime
, DECODE(LAG(nenh_new_status_6) OVER (ORDER BY nenh_trans_date * 10000000 + nenh_trans_time DESC)
, nenh_new_status_6, 0
, 1) AS changed
FROM
ntcenh
ORDER BY
nenh_datetime DESC
) AS vt1
ORDER BY
vt1.nenh_datetime DESC
) AS vt2
ORDER BY
min_nenh_datetime DESC;

结果：

nenh_new_status_6 min_nenh_datetime max_nenh_datetime
SCHED DT            201808080201230   201808080201230
STANDBY             201807230051354   201807230104731
PRODUCTIVE          201807230031912   201807230044208
STANDBY             201807230021546   201807230031138
PRODUCTIVE          201807230005414   201807230013707

在虚拟表vt1中，我使用LAG函数将nenh_new_status_6与前一行进行比较，按计算时间排序。如果它们相等，我将零分配给列changed，否则我将分配1。在虚拟表vt2中，我计算列changed的累积和，因此对于列status_group中的每个范围的nenh_new_status_6，我具有不同的值。最后，针对每个status_group计算出nenh_datetime的MIN和MAX值。

我可能过于复杂化了这个解决方案，但它似乎确实给出了你想要的结果。