时间戳

首页 > 解决方案 > SQL中如何使用over-partition by query获取当前值、平均值和最大值？

sql - SQL中如何使用over-partition by query获取当前值、平均值和最大值？

问题描述

我有这张表，它显示了设备在某个区域和特定位置所做的点。

working_date    device   points   area   location
19-06-2020        a        1       x       xa   
19-06-2020        a        2       x       xa 
19-06-2020        a        3       x       xa 
19-06-2020        a        4       x       xa
20-06-2020        a        5       x       xa
20-06-2020        a        6       x       xa
20-06-2020        a        7       x       xa
20-06-2020        a        8       x       xa
20-06-2020        a        9       x       xa

我想获得按区域和位置分组的当前、平均和最大点。如果我选择任何一天，当前数量将显示最近工作日期的数量。同时，平均数量将显示设备工作的总体平均值。最后，最大数量将显示设备完成的总体最大点。

根据我上面的表格，如果我选择 21-06-2020 那么期望的结果：

working_date  area  location   device   current_qty  avg_qty   max_qty
21-06-2020     x       xa        a         5           4,5        5

平均数量来自 total_qty / total_of_date，而最大数量来自所有日期的最大数量。

到目前为止我建立的查询是：

select t1.working_date, t1.device, t1.area, t1.location, t1.points_qty, t1.total_date,
sum(t1.pile_qty) over(partition by t1.working_date) / sum(t1.total_date) over(partition by t1.working_date) as avg_qty,
max(t1.pile_qty) over(partition by t1.working_date) as max_qty
from (
select working_date, device, points, area, location, count(points) as points_qty, count(distinct working_date) as total_date 
from table1 group by device, area, location
group by working_date, device, points, area, location) t1
group by working_date, device, points, area, location, pile_qty, total_date

通过上面的查询，我得到：

working_date  area  location   device   current_qty  avg_qty   max_qty
21-06-2020     x       xa        a         5           5          5

我应该如何编写查询以获得所需的结果？

提前致谢。

标签： sqlpostgresqlwindow-functions

解决方案

演示：db<>小提琴

SELECT
    *,
    AVG(current_qty) OVER () as avg_qty,             -- 2
    MAX(current_qty) OVER () as max_qty
FROM (
    SELECT 
        working_date,
        area,
        location,
        device,
        COUNT(*) as current_qty                      -- 1
    FROM mytable
    GROUP BY working_date, device, area, location    -- 1
) s
WHERE working_date <= '2020-06-21'                   -- 3
ORDER BY working_date DESC
LIMIT 1
  1. 对值进行正常分组working_date以计算日期qty值。
  2. 使用qty整个分组数据集的这些值，使用无限制的窗口函数将 theavg和qty 值添加到记录中max
  3. 要查找给定日期的最新数据集：过滤具有相同或较小日期值的所有记录，将这些日期中的最新日期排序到顶部，并使用限制仅返回最顶部的日期。

仅当您的区域、位置和设备值与示例中的每条记录相同时，分组才能正常工作。如果它们不同，您可以使用COUNT()as 窗口函数而不是组聚合来将值添加到每条记录：

演示：db<>小提琴

SELECT
    *,
    AVG(current_qty) OVER () as avg_qty,
    MAX(current_qty) OVER () as max_qty
FROM (
    SELECT 
        working_date,
        area,
        location,
        device,
        COUNT(*) OVER (PARTITION BY working_date) as current_qty
    FROM mytable
) s
WHERE working_date <= '2020-06-21'
ORDER BY working_date DESC
LIMIT 1

但是，在这种情况下，不清楚2020-06-20应该获取该组的五个记录中的哪一个。您必须应用您的订单标准来将预期的订单放在顶部。

推荐阅读