如何获得上周(7天)甲骨文的每日记录计数

Question

你好，我正在使用下面的sql查询来获取过去24小时内每小时记录计数

WITH date_range
  AS (SELECT TRUNC(sysdate - (rownum/24),'HH24') as the_hour
        FROM dual
      CONNECT BY ROWNUM <= 24),
     the_data
  AS (SELECT TRUNC(systemdate, 'HH24')  AS log_date, count(*) AS num_obj
        FROM transactionlog where merchantcode='merc0003'
      GROUP BY TRUNC(systemdate, 'HH24'))   
SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(trans_log.num_obj,0)
FROM date_range dr LEFT OUTER JOIN the_data  trans_log
     ON trans_log.log_date =  dr.the_hour
ORDER BY dr.the_hour DESC ;  

我试着在过去7天里每天在甲骨文中获得记录计数， ..某个人能指导我通过改变上面的查询来获得最后7天的结果吗？

我试着跟随过去的7天，但到目前为止还没有收获：

WITH date_range
  AS
(SELECT TRUNC(sysdate - (7)) as the_hour
        FROM dual
      CONNECT BY ROWNUM <= 7),
     the_data
  AS (SELECT TRUNC(systemdate, 'HH24')  AS log_date, count(*) AS num_obj
        FROM transactionlog where merchantcode='merc0003'
      GROUP BY TRUNC(systemdate, 'HH24'))   
SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(trans_log.num_obj,0)
FROM date_range dr LEFT OUTER JOIN the_data  trans_log
     ON trans_log.log_date =  dr.the_hour
ORDER BY dr.the_hour DESC ; 

Answer 1

您的错误是，您没有添加天数；您正在检索同一天七次。在当天添加level (或level - 1，任意您想要的)：

SELECT TRUNC(sysdate) - 7 + level AS the_hour
FROM dual
CONNECT BY ROWNUM <= 7

Answer 2

我很惊讶你最初的CTE居然成功了。我从未听说过将CONNECT BY和ROWNUM一起使用(虽然在谷歌上做了一些工作，但我知道它在某些条件下工作。)不过，我确信这是一种滥用)。要获得过去七天的日期范围，您需要以下内容：

WITH date_range AS (
    -- If you want to include today's date, add +1 to the date below
    SELECT TRUNC(SYSDATE - LEVEL) AS the_date
      FROM dual
   CONNECT BY LEVEL <= 7
)

将其与现有查询结合起来：

WITH date_range AS (
    SELECT TRUNC(SYSDATE - LEVEL) AS the_date
      FROM dual
   CONNECT BY LEVEL <= 7
), the_data AS (
    -- You still had 'HH24' here in the call to TRUNC()!
    SELECT TRUNC(systemdate) AS log_date, count(*) AS num_obj
      FROM transactionlog
     WHERE merchantcode = 'merc0003'
     GROUP BY TRUNC(systemdate)
)
SELECT dr.the_date, COALESCE(trans_log.num_obj, 0)
  FROM date_range dr LEFT JOIN the_data trans_log
    ON dr.the_date = trans_log.log_date
 ORDER BY dr.the_date DESC;

顺便说一句，您只需要为您的CTE使用别名；您不需要给它们提供全名就可以再次使用别名。