Результаты группового запроса по месяцам и годам в postgresql

Ответ 1

select to_char(date,'Mon') as mon,
       extract(year from date) as yyyy,
       sum("Sales") as "Sales"
from yourtable
group by 1,2

По просьбе Раду я объясню этот запрос:

to_char(date,'Mon') as mon,: преобразует атрибут "date" в заданный формат короткой формы месяца.

extract(year from date) as yyyy: функция "extract" Postgresql используется для извлечения года YYYY из атрибута "date".

sum("Sales") as "Sales": Функция SUM() объединяет все значения "Sales" и предоставляет псевдоним с учетом регистра, при этом чувствительность к регистру поддерживается с использованием двойных кавычек.

group by 1,2: Функция GROUP BY должна содержать все столбцы из списка SELECT, которые не являются частью агрегата (ака, все столбцы не входят в функции SUM/AVG/MIN/MAX и т.д.). Это говорит о том, что SUM() следует применять для каждой уникальной комбинации столбцов, которые в этом случае являются столбцами месяца и года. Часть "1,2" является сокращением вместо использования псевдонимов столбцов, хотя, вероятно, лучше всего использовать полные выражения "to_char (...)" и "extract (...)" для удобочитаемости.

Ответ 2

Я не могу поверить, что в принятом ответе было так много стимулов - это ужасный метод.

Здесь правильный способ сделать это, date_trunc:

   SELECT date_trunc('month', txn_date) AS txn_month, sum(amount) as monthly_sum
     FROM yourtable
 GROUP BY txn_month

Это плохая практика, но вам может быть прощено, если вы используете

 GROUP BY 1

в очень простом запросе.

Вы также можете использовать

 GROUP BY date_trunc('month', txn_date)

если вы не хотите выбирать дату.

Ответ 3

to_char фактически позволяет вам вытащить Год и месяц одним махом!

select to_char(date('2014-05-10'),'Mon-YY') as year_month; --'May-14'
select to_char(date('2014-05-10'),'YYYY-MM') as year_month; --'2014-05'

или в случае примера пользователя выше:

select to_char(date,'YY-Mon') as year_month
       sum("Sales") as "Sales"
from some_table
group by 1;

Ответ 4

bma ответ велик! Я использовал его с ActiveRecords, вот он, если кому-то это нужно в Rails:

Model.find_by_sql(
  "SELECT TO_CHAR(created_at, 'Mon') AS month,
   EXTRACT(year from created_at) as year,
   SUM(desired_value) as desired_value
   FROM desired_table
   GROUP BY 1,2
   ORDER BY 1,2"
)

Ответ 5

Postgress имеет несколько советов для временных меток в postgress:

временная метка без часового пояса - (желательно хранить метки времени UTC) Вы находите ее в хранилище многонациональных баз данных. Клиент в этом случае позаботится о смещении часового пояса для каждой страны.

временная метка с часовым поясом. Смещение временной зоны уже включено в метку времени.

В некоторых случаях ваша база данных не использует часовой пояс, но вам по-прежнему необходимо группировать записи по локальному часовому поясу и летнему времени (например, https://www.timeanddate.com/time/zone/romania/bucharest)

Чтобы добавить часовой пояс, вы можете использовать этот пример и заменить смещение часового пояса на ваше.

"your_date_column" at time zone '+03'

Чтобы добавить смещение Летнего времени +1 к DST, вам нужно проверить, попадает ли ваша временная метка в летнее летнее время. Поскольку эти интервалы меняются с 1 или 2 днями, я буду использовать аппроксимацию, которая не влияет на записи конца месяца, поэтому в этом случае я могу игнорировать каждый год точный интервал.

Если требуется более точный запрос, вам нужно добавить условия для создания большего числа случаев. Но, грубо говоря, это будет отлично работать в расщеплении данных в месяц в отношении часового пояса и SummerTime, когда вы найдете временную метку без часовой пояс в своей базе данных:

SELECT 
    "id", "Product", "Sale",
    date_trunc('month', 
        CASE WHEN 
            Extract(month from t."date") > 03 AND
            Extract(day from t."date") > 26 AND
            Extract(hour from t."date") > 3 AND
            Extract(month from t."date") < 10 AND
            Extract(day from t."date") < 29 AND
            Extract(hour from t."date") < 4
        THEN 
            t."date" at time zone '+03' -- Romania TimeZone offset + DST
        ELSE
            t."date" at time zone '+02' -- Romania TimeZone offset 
        END) as "date"
FROM 
    public."Table" AS t
WHERE 1=1
    AND t."date" >= '01/07/2015 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
    AND t."date" < '01/07/2017 00:00:00'::TIMESTAMP WITHOUT TIME ZONE
GROUP BY date_trunc('month', 
    CASE WHEN 
        Extract(month from t."date") > 03 AND
        Extract(day from t."date") > 26 AND
        Extract(hour from t."date") > 3 AND
        Extract(month from t."date") < 10 AND
        Extract(day from t."date") < 29 AND
        Extract(hour from t."date") < 4
    THEN 
        t."date" at time zone '+03' -- Romania TimeZone offset + DST
    ELSE
        t."date" at time zone '+02' -- Romania TimeZone offset 
    END)