postgresql 表中每天值的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14369526/
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
Sum of values per day in a table
提问by user1970850
My table:
我的表：
Dataid date register_type read_value 77 2012-08-15 gen 20 77 2012-08-15 solar 48 77 2012-08-16 gen 39 77 2012-08-16 gen 22 80 2012-07-11 gen 11 80 2012-07-12 id 23 91 2012-02-01 id 4 91 2012-02-01 gen 59 91 2012-02-08 gen 18
I would like, for each day, to do the sum of the read_valuesfor only the "gen" register_type. I basically want the query to return the following table:
我想，对于每一天，read_values只为“gen”做总和register_type。我基本上希望查询返回下表：
dataid date daily_value 77 2012-08-15 20.00 77 2012-08-16 61.00 80 2012-07-11 11.00 91 2012-02-01 59.00 91 2012-02-08 18.00
I tried the following query, but it does not work:
我尝试了以下查询，但它不起作用：
select
dataid,
date_trunc('day', timestamp_localtime) as truncated_day,
substring(cast(date_trunc('day', timestamp_localtime) as text)
from 1 for 10) as date,
sum(read_value) as daily_gen
where register_type like ‘%gen%'
from table
group by dataid, date_trunc('day', timestamp_localtime)
order by dataid, truncated_day
How would I write this query?
我将如何编写此查询？
回答by Erwin Brandstetter
Works in Postgres:
在 Postgres 中工作：
SELECT dataid, date, sum(read_value) AS daily_value
FROM tbl
WHERE register_type = 'gen'
GROUP BY 1,2
ORDER BY 1,2
Or is your column named dateactually something else than a date?
If it's actually a timestamp, replace datein my query with date::date(casting the timestampto date) and it should work.
(You shouldn't use reserved wordslike dateas identifiers to begin with, even if Postgres allows it.)
或者您的专栏date实际上不是日期名称？
如果它实际上是 a timestamp，则date在我的查询中替换为date::date（将timestampto转换为date），它应该可以工作。
（即使 Postgres 允许，您也不应该使用诸如标识符之类的保留字date作为开头。）
回答by Martín Valdés de León
In SQL Server, the following would work:
在 SQL Server 中，以下内容将起作用：
SELECT SUM(read_value) AS daily_value, dataid, date
FROM (SELECT *, CONVERT(VARCHAR(10),date,105) AS newdate FROM Table)
WHERE type = 'gen'
GROUP BY newdate
For MySQL, use this instead:
对于 MySQL，请改用它：
SELECT SUM(read_value) AS daily_value, dataid, date
FROM (SELECT *, DATE_FORMAT(newdate, '%d-%m-%Y') AS day FROM Table)
WHERE type = 'gen'
GROUP BY newdate
The subquery is required since GROUP BYdoesn't support the usual aggregate functions. This, however, is needed only if dateis a datetime. If its just a date, then there's no need to extract the just the datefrom it.
子查询是必需的，因为GROUP BY它不支持通常的聚合函数。但是，仅当date是日期时间时才需要。如果它只是一个日期，那么就没有必要从中提取日期。
