mysql - 按月平均 child 记录

Question

我有两个表:

• sales_header(包含一个created_at 日期)
• sales_line

我想统计每年/每月标题的平均行数。

获取每月 header 数的基本查询很简单:

SELECT YEAR(h.created_at), MONTH(h.created_at), COUNT(h.id)
FROM   sales_header h
GROUP BY YEAR(h.created_at), MONTH(h.created_at)
ORDER BY YEAR(h.created_at), MONTH(h.created_at);

获取每个标题的平均行数也很容易:

SELECT ROUND(AVG(base.ChildCount), 0)
FROM (
  SELECT li.header_id AS HeaderId, COUNT(li.id) AS ChildCount
  FROM sales_header h
  JOIN sales_line li ON li.header_id = h.id
  GROUP BY li.header_id
) base;

我只是很难将两者放在一起。

谢谢!

更新

这里有一些示例数据，以及相同的 fiddle :

CREATE TABLE sales_header (
  id INT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE sales_line (
  id INT NOT NULL AUTO_INCREMENT,
  header_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (header_id) REFERENCES sales_header(id)
);

INSERT INTO sales_header(id, created_at) VALUES
  (1, '2018-12-31T11:11:11'),
  (2, '2019-01-01T11:11:11'),
  (3, '2019-01-05T11:11:11'),
  (4, '2019-01-10T11:11:11'),
  (5, '2019-01-15T11:11:11'),
  (6, '2019-02-15T11:11:11'),
  (7, '2019-03-01T11:11:11'),
  (8, '2019-03-05T11:11:11'),
  (9, '2019-03-10T11:11:11')
;

INSERT INTO sales_line(header_id) VALUES
  (1), (1), (1), (1), (1), (1), (1), (1),
  (2), (2), (2), (2),
  (3), (3), (3), (3), (3), 
  (4), (4),
  (5),
  (6), (6), (6), (6), (6), (6),
  (7), (7),
  (8), (8),
  (9), (9), (9), (9), (9), (9), (9), (9), (9), (9), (9), (9), (9)
;

http://sqlfiddle.com/#!9/e33a94

我想说的是:

| Year | Month | Average Lines Per Header |
| ---- | ----- | ------------------------ |
| 2018 | 12    | 8                        |
| 2019 | 1     | 3                        |
| 2019 | 2     | 6                        |
| 2019 | 3     | 6                        |

Answer 1

将行计数加入到标题表中。要获取行数，请按 header_id 和计数汇总 sales_line。

select year(h.created_at), month(h.created_at), count(*), avg(l.linecount)
from sales_header h
join
(
  select header_id, count(*) as linecount
  from sales_line
  group by header_id
) l on l.header_id = h.id
group by year(h.created_at), month(h.created_at)
order by year(h.created_at), month(h.created_at);