PL SQL：每个时间戳的函数

Question

我有一个 16 列表，而第一列是时间戳（即 16/02/2011 00:00:00），并且该表填充有 5 分钟测量值，这意味着每列和日期将有 288 个 5 分钟测量值。如果我想计算，比如说，每天和每月大于 >100 毫秒的“RTTD”列的值，解决方案是什么？

Answer 1

我创建了一个简单的表（我没有关心所有十六列）：

SQL> desc t23
 Name                    Null?    Type
 ----------------------- -------- -----------------
 TS                               TIMESTAMP(6)
 RTTD                             NUMBER
 STN_ID                           NUMBER

SQL>

这个破烂的匿名块生成四个站四天的读数：

declare
    dt timestamp := trunc(systimestamp, 'MM');
begin

    for r in 1..1152 loop
        insert into t23 values (dt, round(dbms_random.value(0,120)), 11);
        insert into t23 values (dt, round(dbms_random.value(0,120)), 22);
        insert into t23 values (dt, round(dbms_random.value(0,99)), 33);
        if r < 600 then
            insert into t23 values (dt, round(dbms_random.value(0,120)), 44);
        else
            insert into t23 values (dt, round(dbms_random.value(0,80)), 44);
        end if;
        dt := dt + interval '5' minute;
            end loop;
end;
/

因此这个查询将总结四天内四个站的读数。 COUNT() 忽略 NULL 值，并且 SELECT 利用这一点：对于任何小于 100 的 RTTD 值，CASE() 返回 null。

SQL> select trunc(ts) as dt
  2         , stn_id
  3         , count(*) as tot_reads
  4         , count(case when rttd >= 100 then rttd else null end) as rttd_100
  5  from t23
  6  group by trunc(ts), stn_id
  7  order by 1, 2
  8  /

DT            STN_ID  TOT_READS   RTTD_100
--------- ---------- ---------- ----------
01-FEB-11         11        288         35
01-FEB-11         22        288         51
01-FEB-11         33        288          0
01-FEB-11         44        288         54
02-FEB-11         11        288         52
02-FEB-11         22        288         48
02-FEB-11         33        288          0
02-FEB-11         44        288         53
03-FEB-11         11        288         51
03-FEB-11         22        288         43
03-FEB-11         33        288          0
03-FEB-11         44        288          2
04-FEB-11         11        288         48
04-FEB-11         22        288         45
04-FEB-11         33        288          0
04-FEB-11         44        288          0

16 rows selected.

SQL>

为了按月汇总，您可以只需将 TRUNC(ts) 替换为 TRUNC(ts, 'MM')，其工作原理如下：

SQL> select systimestamp as now
  2         , trunc(systimestamp) as today
  3         , trunc(systimestamp, 'MM') as fom
  4  from dual
  5  /

NOW                                  TODAY     FOM
------------------------------------ --------- ---------
23-FEB-11 11.39.29.127000 +00:00     23-FEB-11 01-FEB-11

SQL>

Answer 2

不确定你到底想要什么。问题定义太模糊了，我不确定你所说的那个表究竟是怎样的......

无论如何，我想它会是这样的：

SELECT
    EXTRACT(YEAR FROM FIRST_COLUMN) AS YEAR_,
    EXTRACT(MONTH FROM FIRST_COLUMN) AS MONTH_,
    EXTRACT(DAY FROM FIRST_COLUMN) AS DAY_,
    COUNT(*) AS TOTAL
FROM
    SOME_TABLE
WHERE
    RTTD > 100
GROUP BY
    EXTRACT(YEAR FROM FIRST_COLUMN),
    EXTRACT(MONTH FROM FIRST_COLUMN),
    EXTRACT(DAY FROM FIRST_COLUMN),