gpt4 book ai didi

每组多个组的SQL总和

转载 作者:行者123 更新时间:2023-11-29 11:45:47 24 4
gpt4 key购买 nike

在我之前的问题中有一个相当大的错误

select earliest date from multiple rows

horse_with_no_name 的回答返回了一个完美的结果,我非常感激,但是我最初的问题是错误的,所以我真的很抱歉;如果您查看下表;

circuit_uid  |customer_name     |rack_location  |reading_date   | reading_time | amps | volts  |  kw  | kwh | kva  |  pf  |  key --------------------------------------------------------------------------------------------------------------------------------------cu1.cb1.r1    | Customer 1       | 12.01.a1      | 2012-01-02   | 00:01:01     | 4.51 | 229.32 | 1.03 |  87 | 1.03 | 0.85 |    15cu1.cb1.r1    | Customer 1       | 12.01.a1      | 2012-01-02   | 01:01:01     | 4.18 | 230.3  | 0.96 |  90 | 0.96 | 0.84 |    16cu1.cb1.r2    | Customer 1       | 12.01.a1      | 2012-01-02   | 00:01:01     | 4.51 | 229.32 | 1.03 |  21 | 1.03 | 0.85 |    15cu1.cb1.r2    | Customer 1       | 12.01.a1      | 2012-01-02   | 01:01:01     | 4.18 | 230.3  | 0.96 |  23 | 0.96 | 0.84 |    16cu1.cb1.s2    | Customer 2       | 10.01.a1      | 2012-01-02   | 00:01:01     | 7.34 | 228.14 | 1.67 | 179 | 1.67 | 0.88 | 24009cu1.cb1.s2    | Customer 2       | 10.01.a1      | 2012-01-02   | 01:01:01     | 9.07 |  228.4 | 2.07 | 182 | 2.07 | 0.85 | 24010cu1.cb1.s3    | Customer 2       | 10.01.a1      | 2012-01-02   | 00:01:01     | 7.34 | 228.14 | 1.67 | 121 | 1.67 | 0.88 | 24009cu1.cb1.s3    | Customer 2       | 10.01.a1      | 2012-01-02   | 01:01:01     | 9.07 |  228.4 | 2.07 | 124 | 2.07 | 0.85 | 24010cu1.cb1.r1    | Customer 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    | Customer 3       | 01.01.a1      | 2012-01-02   | 01:01:01     | 6.61 | 228.29 | 1.51 | 226 | 1.51 | 0.88 | 48004cu1.cb1.r4    | Customer 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    | Customer 3       | 01.01.a1      | 2012-01-02   | 01:01:01     | 6.61 | 228.29 | 1.51 | 217 | 1.51 | 0.88 | 48004

As you can see each customer now has multiple circuits. So the result would now be the sum of each of the earliest kwh readings for each circuit per customer, so the result in this table would be;

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)

每个客户将有 2 个以上的电路,读数可能会在不同时间发生,因此需要“最早”读数。

有人对修改后的问题有什么建议吗?

CentOs/Redhat 上的 PostgreSQL 8.4。

最佳答案

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 求和。

索引

A multi-column index像下面这样将使这个非常很快:

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

关于每组多个组的SQL总和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13433735/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com