sql - 获取值大于平均借入率的日期

Question

我有一个名为 BOOK 的表(memberId、ISBN、dateBorrowed)例如:

   isbn       |    memberId |   borrowed   -------+---------------+-------------+----9998-01-101-9 |             |           9998-01-101-9 |             |            9998-01-101-9 |             |            9998-01-101-9 |        1000 | 2018-10-029998-01-101-9 |        1010 | 2018-09-049998-01-101-9 |        1021 | 2018-09-149998-01-101-9 |             |            9998-01-101-9 |        1001 | 2018-10-02

I have to SELECT all dates, where total count of borrowed books per day is larger, than per all days in average. How to do it?

I have selected date and how many times was it picked by:

SELECT borrowed, COUNT(*) AS dates 
  FROM BOOK
  WHERE borrowed IS NOT NULL
  GROUP BY borrowed;

我写的另一个查询是计算平均值:

SELECT SUM(dates)/COUNT(borrowed) AS average 
  FROM (
    SELECT borrowed, COUNT(*) AS dates 
      FROM BOOKS
      WHERE borrowed IS NOT NULL GROUP BY borrowed
  ) AS average;

现在，如何将这两个续集连接成一个清晰的续集？

Answer 1

使用窗口函数可以帮到你很多:https://www.postgresql.org/docs/current/static/tutorial-window.html

demo: db<>fiddle

我的测试数据:

isbn             borrowed
9998-01-101-1    2018-08-01
9998-01-101-2    2018-08-01
9998-01-101-3    2018-08-01
9998-01-101-4    2018-08-01
9998-01-101-5    2018-08-01
9998-01-101-1    2018-08-02
9998-01-101-2    2018-08-02
9998-01-101-3    2018-08-02
9998-01-101-4    2018-08-03
9998-01-101-5    2018-08-03
9998-01-101-1    2018-08-04
9998-01-101-2    2018-08-04
9998-01-101-3    2018-08-04
9998-01-101-4    2018-08-04
9998-01-101-5    2018-08-05
9998-01-101-1    2018-08-05

查询:

   SELECT 
        * 
    FROM (
        SELECT 
            *, 
            borrowed_all_time::decimal / COUNT(*) OVER () as avg_borrows_per_day    -- D
        FROM (
            SELECT DISTINCT                                                         -- C
                borrowed,
                COUNT(*) OVER (PARTITION BY borrowed) as borrowed_on_day,           -- A
                COUNT(*) OVER () as borrowed_all_time                               -- B
            FROM book
        )s 
    )s
    WHERE borrowed_on_day > avg_borrows_per_day                                     -- E

A:这个窗口函数计算每个借用日期的行数

B:此窗口函数计算所有行，等于计算所有时间的借用。

目前的结果是这样的:

borrowed      borrowed_on_day   borrowed_all_time
2018-08-01    5                 16
2018-08-01    5                 16
2018-08-01    5                 16
2018-08-01    5                 16
2018-08-01    5                 16
2018-08-02    3                 16
2018-08-02    3                 16
2018-08-02    3                 16
2018-08-03    2                 16
2018-08-03    2                 16
2018-08-04    4                 16
2018-08-04    4                 16
2018-08-04    4                 16
2018-08-04    4                 16
2018-08-05    2                 16
2018-08-05    2                 16

C:因为我们不需要重复项，所以我们使用 DISTINCT

消除它们

D:在消除所有并列行后对所有行进行计数，得出不同天数的计数。将所有时间的借款数除以平均每天的借款数。 decimal 转换是必需的。它将整数除法 (16/5 == 3) 转换为浮点除法 (16/5 == 3.2)

E:现在我们可以过滤当天的借款数> 平均每天的借款数。

结果:

borrowed
2018-08-01
2018-08-04