mysql - 每个月的平均子记录

我有两张桌子：
sales_header（包含日期）
created_at
我想计算每年/每月页眉上的平均行数。
每月获取标题计数的基本查询很简单：

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;

我只是很难把两者结合起来。
谢谢！
更新
以下是一些样本数据，以及一些相同的数据：

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                        |

最佳答案

将行计数联接到标题表。要获取行计数，请按标题id和计数聚合销售行。

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);

关于mysql - 每个月的平均子记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58919875/