每组的 SQL 合计多个组的总和

Question

在我的上一个问题中出现了一个相当大的错误

从多行中选择最早日期

horse_with_no_name给出了一个完美的答案，我非常感激，但是我自己的初始问题是错误的，我真的很抱歉；如果您看一下下面的表格；

电路编号 | 客户名称   | 机架位置 | 读取日期    | 读取时间  | 安培数 | 伏特数 | 千瓦数 | 千瓦时数 | 千伏安数 | 功率因数 | 键值
------------------------------------------------------------------------------------------------------------------------
cu1.cb1.r1 | 客户1     | 12.01.a1 | 2012-01-02 | 00:01:01 | 4.51   | 229.32 | 1.03   | 87      | 1.03    | 0.85    | 15  
cu1.cb1.r1 | 客户1     | 12.01.a1 | 2012-01-02 | 01:01:01 | 4.18   | 230.3  | 0.96   | 90      | 0.96    | 0.84    | 16  
cu1.cb1.r2 | 客户1     | 12.01.a1 | 2012-01-02 | 00:01:01 | 4.51   | 229.32 | 1.03   | 21      | 1.03    | 0.85    | 15  
cu1.cb1.r2 | 客户1     | 12.01.a1 | 2012-01-02 | 01:01:01 | 4.18   | 230.3  | 0.96   | 23      | 0.96    | 0.84    | 16  
cu1.cb1.s2 | 客户2     | 10.01.a1 | 2012-01-02 | 00:01:01 | 7.34   | 228.14 | 1.67   | 179     | 1.67    | 0.88    | 24009
cu1.cb1.s2 | 客户2     | 10.01.a1 | 2012-01-02 | 01:01:01 | 9.07   | 228.4  | 2.07   | 182     | 2.07    | 0.85    | 24010
cu1.cb1.s3 | 客户2     | 10.01.a1 | 2012-01-02 | 00:01:01 | 7.34   | 228.14 | 1.67   | 121     | 1.67    | 0.88    | 24009
cu1.cb1.s3 | 客户2     | 10.01.a1 | 2012-01-02 | 01:01:01 | 9.07   | 228.4  | 2.07   | 124     | 2.07    | 0.85    | 24010
cu1.cb1.r1 | 客户3     | 01.01.a1 | 2012-01-02 | 00:01:01 | 7.32   | 229.01 | 1.68   | 223     | 1.68    | 0.89    | 48003
cu1.cb1.r1 | 客户3     | 01.01.a1 | 2012-01-02 | 01:01:01 | 6.61   | 228.29 | 1.51   | 226     | 1.51    | 0.88    | 48004
cu1.cb1.r4 | 客户3     | 01.01.a1 | 2012-01-02 | 00:01:01 | 7.32   | 229.01 | 1.68   | 215     | 1.68    | 0.89    | 48003
cu1.cb1.r4 | 客户3     | 01.01.a1 | 2012-01-02 | 01:01:01 | 6.61   | 228.29 | 1.51   | 217     | 1.51    | 0.88    | 48004

正如您所看到的，每个客户现在都有多个电路。因此，结果现在将是每个客户每个电路最早kwh读数的总和，因此此表中的结果将为：

customer_name | kwh(sum)
--------------+-----------
customer 1    | 108      (the result of 87 + 21)  
customer 2    | 300      (the result of 179 + 121)  
customer 3    | 438      (the result of 223 + 215)   

每个客户将有两个以上的电路，读数时间也会因此而不同，因此需要“最早”的读数。

是否有任何关于修改问题的建议？

CentOs/Redhat上的PostgreSQL 8.4。

- Alan Ennis

Answer 1

SELECT customer_name, sum(kwh) AS kwh_total
FROM  (
    SELECT DISTINCT ON (customer_name, circuit_uid)
           customer_name, circuit_uid, kwh
    FROM   readings
    WHERE  reading_date = '2012-01-02'::date
    ORDER  BY customer_name, circuit_uid, reading_time
    ) x
GROUP  BY 1

与 before 相同，只需选择最早的 （customer_name，circuit_uid）。然后按 customer_name 汇总。

索引

像以下这样的 多列索引 可使此操作非常快：

CREATE INDEX readings_multi_idx
ON readings(reading_date, customer_name, circuit_uid, reading_time);

- Erwin Brandstetter

---

@AlanEnnis：你能用EXPLAIN ANALYZE跑个快速测试吗？会很有趣的。 - Erwin Brandstetter

Erwin，您的结果为：总运行时间：21.058毫秒（8行） Horse的结果为：总运行时间：20.623毫秒（10行） 有关该日期的总行数为432行。 你们俩都很棒，谢谢。 - Alan Ennis

@AlanEnnis：感谢反馈。可能在这种情况下，“DISTINCT ON”版本有一些不必要的排序开销。对于需要有序输出的简单情况，这个变体通常更快。但对于像你这样的小数据集来说，这真的没有什么影响 - 除非你经常调用它。另外：由于我在答案中添加了索引，因此这将比任何其他方法都更快。 - Erwin Brandstetter

Erwin，使用创建索引的结果，您的查询总运行时间为9.325毫秒（10行），而horse的查询总运行时间为9.523毫秒（12行）。在生产环境中，每个单独日期将有20或30倍的行数，因此这将产生差异。感谢您提供的索引，我会将其保存到生产数据库中。 - Alan Ennis

Answer 2

这是对您原来问题的扩展：

select customer_name,
       sum(kwh)
from (
   select customer_name,
          kwh,
          reading_time,
          reading_date,
          row_number() over (partition by customer_name, circuit_uid order by reading_time) as rn
   from readings
   where reading_date = date '2012-01-02'
) t
where rn = 1
group by customer_name

请注意外部查询中的新sum()和内部查询中更改的partition by定义（与您之前的问题相比），现在计算每个circuit_uid的第一次读数（而不是每个客户的第一次读数）。

- user330315