sql - 尝试选择数据，然后对 mariadb 中的所选数据进行平均

Question

SELECT counts FROM (SELECT COUNT( ch_id) AS counts FROM tbl_warrants_checked WHERE status= "active" GROUP BY dateChecked);

标签： sqlxamppmariadb

Answer 1

这使用 MariaDB 10.5，但应该适用于 ~10.2.2 之后的任何版本。

如果我们假设每天都有数据，或者我们不关心没有数据的天数，下面将计算自 1 个月前（数据的最后一个月）以来每天的平均检查次数：

-- Average of counts per day since ~1 month ago.
-- Only for days present in the set
SELECT AVG(counts) AS average
  FROM (
          SELECT COUNT(ch_id) AS counts
            FROM tbl_warrants_checked
           WHERE status = 'active'
             AND dateChecked > (current_date - INTERVAL '1' MONTH)
           GROUP BY dateChecked
       ) AS x
;

样本结果：

+---------+
| average |
+---------+
|  3.2941 |
+---------+

如果我们希望考虑缺失的天数并将其视为 0，则以下生成自 1 个月前以来的天数，并将LEFT JOIN其与每天找到的计数一起生成：

-- Average of counts per day since ~1 month ago.
-- Treat missing days as 0
WITH RECURSIVE dates (date) AS (
          SELECT current_date UNION ALL
          SELECT date - INTERVAL '1' DAY FROM dates
           WHERE date > (current_date - INTERVAL '1' MONTH)
     )
SELECT AVG(COALESCE(counts, 0)) AS average
  FROM dates AS d
  LEFT JOIN (
          SELECT dateChecked
               , COUNT(ch_id) AS counts
            FROM tbl_warrants_checked
           WHERE status = 'active'
             AND dateChecked > (current_date - INTERVAL '1' MONTH)
           GROUP BY dateChecked
       ) AS x
    ON d.date = x.dateChecked
;

样本结果：

+---------+
| average |
+---------+
|  1.7500 |
+---------+

使用产生上述结果的数据的工作测试用例

注意：我们也可以计算缺失的天数，并在没有递归和的情况下在最终计算中使用它JOIN。还有另一种简化。