Leftium Leftium - 5 months ago 13
SQL Question

How does date manipulation/comparison/grouping work in SQL queries?

I need to analyze an SQL query (and construct its equivalent in MDX). I'm not familiar with SQL and can't access the database, so there are 5 simple things I can't figure out:


  1. What does the part
    WHERE idate BETWEEN trunc(SYSDATE, 'iw')-7 AND trunc(SYSDATE, 'iw')-3
    mean? Specifically:


    • What does subtracting 7 from
      trunc(SYSDATE, 'iw')
      do? Subtract 7 weeks or 7 days? I understand the
      trunc(...)
      expression is a value 0-53 corresponding to the week of the year, but it seems to clash with the label "previous week" and stated purpose of the query.

    • How does SQL compare dates? Are the values from
      trunc(...)
      evaluated as dates during comparison?


  2. The query seems to group rows together if they happened in the same minute. However, the few rows of output I can see have 10-minute granularity (00:00, 00:10, 00:20, etc.) Is there something in the query that groups rows into 10 minute intervals, or is this a result of the input data?

  3. Why are calls to
    substr()
    and
    to_char()
    and needed in the group by condition? What would happen if
    trunc(idate, 'HH24:MI')
    was used instead?

  4. What does the
    pm
    do? There is also a
    cm
    that seems to have a similar function. Are these part of the temporary table names?

  5. Finally, how do the hash marks (#) affect this query? I read it might be to signify temporary tables. If so, are these temporary tables created manually, or does something in the query cause them to be created?






For reference here is the query. (On a Oracle database, if it makes any difference.) Its purpose is to "analyze how firewall accept events are trending compared to last week":

SELECT 'Previous Week Average' AS term ,
Substr(To_char(idate, 'HH24:MI'), 0, 4)
|| '0' AS event_time ,
Round(Avg(tot_accept)) AS cnt
FROM (
SELECT *
FROM st_event_100_#yyyymm-1m#
WHERE idate BETWEEN trunc(SYSDATE, 'iw')-7 AND trunc(SYSDATE, 'iw')-3 #stat_monitor_group_query#
UNION ALL
SELECT *
FROM st_event_100_#yyyymm#
WHERE idate BETWEEN trunc(SYSDATE, 'iw')-7 AND trunc(SYSDATE, 'iw')-3 #stat_monitor_group_query# ) pm
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4)
|| '0'
UNION ALL
SELECT 'Today' AS term ,
substr(to_char(idate, 'HH24:MI'), 0, 4)
|| '0' AS event_time ,
round(avg(tot_accept)) AS cnt
FROM st_event_100_#yyyymm# cm
WHERE idate >= trunc(SYSDATE) #stat_monitor_group_query#
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4)
|| '0'
ORDER BY term DESC,
event_time ASC

Answer
  1. iw truncates the date to the first day of the calendar week as defined by the ISO 8601 standard, which is Monday. When you subtract numbers from the date, it is always the number of days. So, idate BETWEEN trunc(SYSDATE, 'iw')-7 AND trunc(SYSDATE, 'iw')-3 gives you those dates that fall between previous week's Monday and Friday.

  2. to_char(idate, 'HH24:MI') gives you the time(hour and minute) part in 24hr format. Ex: 14:33. By using substrin to extract only 4 characters, you are actually getting 14:3. So yes, this groups with a granularity of 10 mins.

  3. You cannot write trunc(idate, 'HH24:MI'). It can only have 1 precision specifier.

    If you write trunc(idate,'HH24'), it truncates to the hour. If you use MI, it truncates to the minute. So, to truncate it to 10 mins is a little tricky.

  4. pm is just an alias for the whole subquery.

    SELECT * FROM st_event_100_#yyyymm-1m# ...... WHERE idate BETWEEN trunc(SYSDATE, 'iw')-7 AND trunc(SYSDATE, 'iw')-3 #stat_monitor_group_query#

  5. # is part of the table anme in your query. It has no significance as such. But, it might be project/company specific.

Comments