PL SQL: functions per timestamp

I have a 16 colymns table whereas the first column is the timestamp (i.e. 16/02/2011 00:00:00) and the table is populated with 5min measurements meaning that there will be 288 5min measurements per column and date. If i want to count, lets s开发者_如何学Cay, the values of column "RTTD" that are greater than >100 ms every day and every month, what is the solution?

I have created a simple table ( I haven't bothered with all sixteen columns):

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

SQL>

This shonky anonymous block generates four days of readings for four stations:

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;
/

So this query will summarise the readings for the four stations across the four days. COUNT() ignores NULL values, and the SELECT leverages this: the CASE() returns null for any value of RTTD less than 100.

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>

In order to aggregate this by month you simply need to replace TRUNC(ts) with TRUNC(ts, 'MM'), which works like this:

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>

Not sure of what exactly you want. The problem definition is too vague, and I am not sure how exactly is that table you're talking about...

Anyway, I guess it would be something like that:

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),