Oracle Average查询

Question

有人会认为这很简单。我只想要的日期，计数和平均每天为任何时间范围。因此，在下面的示例中，我想要10天内每天的计数和平均值。

SELECT date_added, 10 / customer_cnt AS average
FROM (SELECT TRUNC(date_added) AS date_added, 
             ROW_NUMBER() OVER (PARTITION BY TRUNC(date_added) ORDER BY TRUNC(date_added)) AS  rnum,
             COUNT(*) OVER (PARTITION BY TRUNC(date_added)) AS customer_cnt
     FROM payments_log
     WHERE action = 'get_cc_info'
       AND TRUNC(date_added) >= TRUNC(SYSDATE) - 10)
WHERE rnum = 1;

但我没有得到任何接近我期望的东西。

Answer 1

我不知道“每天平均”是什么意思。我想你只是想要计数：

SELECT TRUNC(date_added) AS date_added, 
        COUNT(*) as day_count
FROM payments_log
WHERE action = 'get_cc_info' AND
      date_added >= TRUNC(SYSDATE) - INTERVAL '10' day
GROUP BY TRUNC(date_added);

如果您想要这段时间内的平均值(即结果集中的一行)，那么您需要如下内容：

SELECT COUNT(*) / 10 as daily_average
       COUNT(*) / COUNT(DISTINCT TRUNC(date_added) daily_average_on_days_with_data
FROM payments_log
WHERE action = 'get_cc_info' AND
      date_added >= TRUNC(SYSDATE) - INTERVAL '10' day;