你的位置:首页 > 数据库

[数据库]查询同一组的最大最小值及明细


数据库环境:Oracle11g

现有某监测站的监测数据如下表。要求:查询出每个测站的水位最大最小值及最大最小值对应的时间,

如果最大、最小值存在重复,则只显示重复数据中最新时间的那条数据。

实现思路:

  1. 根据测站和水位分组,按最新时间排序,有重复的只取最新时间的数据
  2. 分别求得最大、最小值的所在数据行,然后union

准备建表,导入数据的脚本

create table dse_river_r(stcd varchar2(8),tm date,z number(6,2))insert into dse_river_r values('80520508',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),10.02);insert into dse_river_r values('80520508',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),9.98);insert into dse_river_r values('80520508',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),10.02);insert into dse_river_r values('60407758',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),9.00);insert into dse_river_r values('60407758',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),10.00);insert into dse_river_r values('60407758',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),12.00);insert into dse_river_r values('60407725',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),9.42);insert into dse_river_r values('60407725',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),9.42);insert into dse_river_r values('60407725',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),9.42);insert into dse_river_r values('60507426',to_date('2013-07-01 10:00:00','yyyy-mm-dd hh24:mi:ss'),12.00);insert into dse_river_r values('60507426',to_date('2013-07-01 09:00:00','yyyy-mm-dd hh24:mi:ss'),8.32);insert into dse_river_r values('60507426',to_date('2013-07-01 08:00:00','yyyy-mm-dd hh24:mi:ss'),12.00);insert into dse_river_r values('60507426',to_date('2013-07-01 07:00:00','yyyy-mm-dd hh24:mi:ss'),9.10);

View Code

数据有了,现在我们来实现这个查询

1.过滤重复数据

WITH x0   AS (SELECT STCD,        TM,        Z,        ROW_NUMBER () OVER (PARTITION BY stcd, z ORDER BY tm DESC)          AS rn      FROM dse_river_r),   x1   AS (SELECT STCD, TM, Z      FROM x0     WHERE rn = 1)

View Code

2.union最大、最小值所在行的数据集

SELECT STCD, tm, z FROM (SELECT STCD,        tm,        z,        MAX (z) OVER (PARTITION BY stcd) AS z_max     FROM x1) WHERE z = z_maxUNIONSELECT STCD, tm, z FROM (SELECT STCD,        tm,        z,        MIN (z) OVER (PARTITION BY stcd) AS z_min     FROM x1) WHERE z = z_min

View Code

当然,我们也可以对union操作近一步简化,直接去掉union操作

WITH x0   AS (SELECT STCD,        TM,        Z,        ROW_NUMBER () OVER (PARTITION BY stcd, z ORDER BY tm DESC)          AS rn      FROM dse_river_r),   x1   AS (SELECT STCD,        TM,        Z,        MAX (z) OVER (PARTITION BY stcd) AS z_max,        MIN (z) OVER (PARTITION BY stcd) AS z_min      FROM x0     WHERE rn = 1)SELECT STCD, TM, Z FROM x1 WHERE z IN (z_max, z_min)

View Code

好了,现在展示一下查询结果

(本文完)