postgresql sql选择多行的最早日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13433263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql select earliest date for multiple rows
提问by Alan Ennis
I have a database that looks like the following;
我有一个如下所示的数据库；
circuit_uid | customer_name | 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 | 15
cu1.cb1.r1 | Customer 1 | 12.01.a1 | 2012-01-02 | 01:01:01 | 4.18 | 230.3 | 0.96 | 90 | 0.96 | 0.84 | 16
cu1.cb1.s2 | Customer 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 | Customer 2 | 10.01.a1 | 2012-01-02 | 01:01:01 | 9.07 | 228.4 | 2.07 | 182 | 2.07 | 0.85 | 24010
cu1.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 | 48004
What I am trying to do is produce a result that has the KWH reading for each customer from the earliest (min(reading_time)) on that date, the date will be selected by the user in a web form.
我想要做的是生成一个结果，该结果具有从min(reading_time)那个日期最早 ( ) 开始的每个客户的 KWH 读数，用户将在 Web 表单中选择该日期。
The result would be/should be similar to;
结果将/应该类似于;
Customer 1 87
Customer 2 179
Customer 3 223
There are more than the number of rows per day shown here and there are more customers and the number of customers would change regularly.
此处显示的每天行数多于客户，而且客户数量会定期变化。
I do not have much experience with SQL, I have looked at subqueries etc. but I do not have the chops to figure out how arrange it by the earliest reading per customer and then just output the kwhcolumn.
我对 SQL 没有太多经验，我看过子查询等，但我没有能力弄清楚如何通过每个客户的最早阅读来安排它，然后只输出kwh列。
This is running in PostgreSQL 8.4 on Redhat/CentOS.
这是在 Redhat/CentOS 上的 PostgreSQL 8.4 中运行的。
采纳答案by a_horse_with_no_name
select customer_name,
kwh,
reading_date,
reading_time
from (
select customer_name,
kwh,
reading_time,
reading_date,
row_number() over (partition by customer_name order by reading_time) as rn
from readings
where reading_date = date '2012-11-17'
) t
where rn = 1
As an alternative:
作为备选：
select r1.customer_name,
r1.kwh,
r1.reading_date,
r1.reading_time
from readings r1
where reading_date = date '2012-11-17'
and reading_time = (select min(r2.reading_time)
from readings
where r2.customer_name = r1.customer_name
and r2.read_date = r1.reading_date);
But I'd expect the first one to be faster.
但我希望第一个更快。
Btw: why do you store date and time in two separate columns? Are you aware that this could be handled better with a timestampcolumn?
顺便说一句：为什么将日期和时间存储在两个单独的列中？您是否知道使用timestamp列可以更好地处理此问题？
回答by Erwin Brandstetter
This should be among the fastest possible solutions:
这应该是最快的解决方案之一：
SELECT DISTINCT ON (customer_name)
customer_name, kwh -- add more columns as needed.
FROM readings
WHERE reading_date = user_date
ORDER BY customer_name, reading_time
Seems to be another application of:
似乎是另一个应用：
回答by Yogendra Singh
SELECT rt.circuit_uid , rt.customer_name, rt.kwh
FROM READING_TABLE rt JOIN
(SELECT circuit_uid, reading_time
FROM READING_TABLE
WHERE reading_date = '2012-01-02'
GROUP BY customer_uid
HAVING MIN(reading_time) = reading_time) min_time
ON (rt.circuit_uid = min_time.circuit_uid
AND rt.reading_time = min_time.reading_time);
Parameterize the reading_date value in above query.
参数化上述查询中的 reading_date 值。
