Группировка по месяцам sql: delphi — Группировка по месяцам даты SQL
Содержание
delphi — Группировка по месяцам даты SQL
Вопрос задан
Изменён
5 лет 2 месяца назад
Просмотрен
8k раз
Есть некая таблица с датами и прочей инфой. необходимо сгруппировать инфу только по месяцам. Вот что я попробовал
Select наименование, month(дата) FROM таблица1 Group by month(дата), наименование
Данный код группирует, однако только если дата полностью совпадает. Мне необходимо, что бы группировка происходила только по месяцам, то есть записей должно быть всего 12. В чем я ошибся?
- sql
- delphi
- ms-access
12
Ни в чём не ошибся, если нужен анализ продаж по месяцам, то там и так будет одна из агрегатных функций или COUNT
или SUM
и приведённый код будет нормально работать. Если работает только, если дата полностью совпадает, значит не правильно выделяете месяц.
Тонкостей MS-Access не помню, а в PostgreSQL это будет выглядеть следующим образом:
SELECT month, COUNT(*) FROM art --Или SUM(fieldname) JOIN LATERAL EXTRACT(MONTH FROM дата) month ON TRUE --Просто чтобы не писать то же самое в GROUP BY WHERE дата IS NOT NULL GROUP BY month ORDER BY month
Ну а если всё-таки Наименование
, то естественно записей будет не 12, а будет зависеть от количества различный наименований проданных в данные месяца.
Хотя, если из Delphi, то это даже удобнее будет, там в табличке группировку сделаете по месяцам и будет плюсиком красиво разворачиваться каждый месяц с суммой в footer
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Почта
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
sql — Группировка по неделям
Есть 2 столбца: дата и числа.
Как можно сгруппировать таким образом:
месяц | 1 неделя | 2 неделя | 3 неделя | 4 неделя ------------------------------------------------------------------------- Январь | Max(1 неделя) | Max(2 неделя) | Max(3 неделя) | Max(4 неделя)
Имеются в виду недели месяца. Используется Oracle SQL.
- sql
- oracle
- plsql
0
Пусть ответ и старый, но это можно сделать с помощью PIVOT
WITH SAMPLE AS ( SELECT sysdate as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate as AMOUNT_DATE, 50 as AMOUNT FROM dual UNION SELECT sysdate as AMOUNT_DATE, 30 as AMOUNT FROM dual UNION SELECT sysdate - 30 as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate - 150 as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate - 30 as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate - 70 as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate - 60 as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate - 30 as AMOUNT_DATE, 10 as AMOUNT FROM dual UNION SELECT sysdate - 500 as AMOUNT_DATE, 10 as AMOUNT FROM dual) SELECT * FROM ( SELECT * FROM ( SELECT s. AMOUNT, to_char(s.AMOUNT_DATE, 'W') WEEK_DATE, INITCAP(to_char(s.AMOUNT_DATE, 'MONTH')) MONTH_DATE FROM SAMPLE s) PIVOT (MAX(AMOUNT) for WEEK_DATE in (1, 2, 3, 4, 5)))
Вывод:
MONTH_DATE 1 2 3 4 5 ----------------------------------------------------- July (null) (null) (null) 10 (null) June (null) 10 (null) 10 (null) April (null) 10 (null) (null) (null) August (null) (null) 50 (null) (null) March (null) (null) (null) 10 (null)
В выводе получится максимальное значение за неделю в месяце вне зависимости от года.
Создать вспомогательный курсор с номерами недель (что Вы имеете в виду под «неделя», не совсем ясно: или календарная неделя (1-52) или неделя от заданной даты, или ещё что) с группировкой по неделям, а затем сформировать нужную запись из этого курсора.
1
Можно сделать банально так без сводных таблиц и т. д.:
select distinct to_char(t.date1, 'Month') as monthname, max1, -- first week max2, -- second week max3, -- third week max4 -- forth week from test t left join (select trunc(date1, 'mm') as monthdate, max(case when date1 between trunc(date1, 'mm') and trunc(date1, 'mm')+7 then num else null end) as max1, max(case when date1 between trunc(date1, 'mm')+8 and trunc(date1, 'mm')+14 then num else null end) as max2, max(case when date1 between trunc(date1, 'mm')+15 and trunc(date1, 'mm')+21 then num else null end) as max3, max(case when date1 between trunc(date1, 'mm')+22 and trunc(date1, 'mm')+28 then num else null end) as max4 from test group by trunc(date1, 'mm') ) m on trunc(t.date1, 'mm') = m.monthdate
Пример на sqlfiddle.
В MSSQL примерно так
DECLARE @StartDate datetime SET @StartDate = GetDate() SET @StartDate = dbo. StartOfMonth(@StartDate) SELECT i.ID, DATEADD(DAY,i.ID-1,@StartDate) AS [Date] INTO #Temp FROM tmp_Index i WHERE i.ID BETWEEN DAY(@StartDate) AND DAY(dbo.EndOfMonth(@StartDate)) SELECT MAX(ID), DATEPART(week,[Date]) FROM #Temp GROUP BY DATEPART(week,[Date]) DROP TABLE #Temp
2
Зарегистрируйтесь или войдите
Регистрация через Google
Регистрация через Facebook
Регистрация через почту
Отправить без регистрации
Почта
Необходима, но никому не показывается
Отправить без регистрации
Почта
Необходима, но никому не показывается
Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки
Как сгруппировать по месяцам с помощью SQL Server?
спросил
Изменено
1 год, 7 месяцев назад
Просмотрено
147 тысяч раз
У меня есть таблица с этой схемой
ItemID UserID Year IsPaid PaymentDate Amount 1 1 20090 01. 11.2009 300 2 1 2009 0 01.12.2009 342 3 1 2010 0 01.01.2010 243 4 1 2010 0 01.02.2010 2543 5 1 2010 0 01.03.2010 475
Я пытаюсь заставить работать запрос, который показывает итоги за каждый месяц. До сих пор я пробовал DateDiff и вложенные выборки, но ни один из них не дает мне того, что я хочу. Это самое близкое, что я думаю:
DECLARE @start [datetime] = 2010/4/1; ВЫБЕРИТЕ ItemID, IsPaid, (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 и DateDiff (m, PaymentDate, @start) = 0 И UserID = 100) КАК «Апр», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 1 И UserID = 100) КАК «Май», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 2 И UserID = 100) КАК «Июнь», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 3 И UserID = 100) КАК «июль», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 4 И UserID = 100) КАК «Авг», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 5 И UserID = 100) КАК «Сентябрь», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 6 И UserID = 100) КАК «Октябрь», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 7 И UserID = 100) КАК «Ноябрь», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 8 И UserID = 100) КАК «Dec», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 9И UserID = 100) КАК "Ян", (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 10 И UserID = 100) КАК «Фев», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И DateDiff (m, PaymentDate, @start) = 11 И UserID = 100) КАК «Мар» FROM LIVE L INNER JOIN Payments I ON I. LiveID = L.RECORD_KEY ГДЕ UserID = 16178
Но я просто получаю нули, когда должен получать значения. Я что-то пропустил?
- sql
- sql-сервер-2008
- датадифф
3
SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Месяц], SUM(Сумма) [TotalAmount] ОТ Платежи ГРУППИРОВАТЬ ПО ПРЕОБРАЗОВАНИЮ (NVARCHAR (10), PaymentDate, 120) ЗАКАЗАТЬ ПО [Месяц]
Вы также можете попробовать:
SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount] ОТ Платежи ГРУППА ПО DATEPART(Год, Дата платежа), DATEPART(Месяц, Дата платежа) Сортировать по году, месяцу
1
Ограничение размера NVARCHAR до 7, передаваемого в CONVERT для отображения только «ГГГГ-ММ»
SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Месяц], SUM(Amount) [TotalAmount] ОТ Платежи ГРУППА ПО ПРЕОБРАЗОВАНИЮ (NVARCHAR (7), PaymentDate, 120) ЗАКАЗАТЬ ПО [Месяц]
Я предпочитаю комбинировать DATEADD
и DATEDIFF
, например:
Вместе эти две функции обнуляют компонент даты , меньший , чем указанный datepart (т. е. МЕСЯЦ
в этом примере).
Вы можете изменить бит даты на YEAR
, WEEK
, DAY
и т. д., что очень удобно.
Ваш первоначальный SQL-запрос будет выглядеть примерно так (я не могу проверить его, так как у меня нет вашего набора данных, но он должен указать вам правильный путь).
DECLARE @start [datetime] = '2010-04-01'; ВЫБИРАТЬ ID товара, ID пользователя, DATEADD(МЕСЯЦ,DATEDIFF(МЕСЯЦ, 0, Создано),0) [Месяц], Оплачено, СУММА(Сумма) ИЗ ЖИВОГО Л INNER JOIN Платежи I ON I.LiveID = L.RECORD_KEY ГДЕ UserID = 16178 И Дата платежа > @start
Еще одна вещь: столбец Month
вводится как DateTime
, что также является хорошим преимуществом, если вам нужно дополнительно обработать эти данные или, например, отобразить их объект .NET.
Если вам нужно делать это часто, я бы, вероятно, добавил вычисляемый столбец PaymentMonth
в таблицу:
ALTER TABLE dbo. Payments ADD PaymentMonth AS MONTH(PaymentDate) PERSISTED
Он сохраняется и хранится в таблице, поэтому его запросы не снижают производительность. Это 4-байтовое значение INT, так что накладные расходы также минимальны.
Если у вас есть это, вы можете упростить свой запрос, чтобы он выглядел примерно так:
SELECT ItemID, IsPaid, (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И PaymentMonth = 1 И UserID = 100) КАК «Янв», (ВЫБЕРИТЕ СУММУ (сумму) ИЗ платежей, ГДЕ Год = 2010 И PaymentMonth = 2 И UserID = 100) КАК «Фев», .... и так далее ..... ИЗ ЖИВОГО Л INNER JOIN Платежи I ON I.LiveID = L.RECORD_KEY ГДЕ UserID = 16178
1
DECLARE @start [datetime] = 2010/4/1;
Должно быть…
DECLARE @start [datetime] = '2010-04-01';
У вас есть деление 2010 года на 4, затем на 1, а затем преобразование в дату. Это 57,5-й день с 01 января 1900 года.
Попробуйте SELECT @start
после инициализации, чтобы проверить, правильно ли это.
Другой подход, который не требует добавления столбцов к результату, заключается в простом обнулении компонента даты дня
, поэтому 2016-07-13
и 2016-07-16
оба будут равны 2016-07-01
, что сделает их равными по месяцам.
Если у вас есть значение даты
(не datetime
), вы можете обнулить его напрямую:
SELECT DATEADD(день, 1 - DATEPART(день, [Дата]), [Дата]), СЧИТАТЬ(*) ОТ [Стол] ГРУППА ПО DATEADD(день, 1 - DATEPART(день, [Дата]), [Дата])
Если у вас есть значения datetime
, вам нужно будет использовать CONVERT
, чтобы удалить часть времени суток:
ВЫБЕРИТЕ DATEADD(день, 1 - DATEPART(день, [Дата]), CONVERT(дата, [Дата])), СЧИТАТЬ(*) ОТ [Стол] ГРУППА ПО DATEADD(день, 1 - DATEPART(день, [Дата]), CONVERT(дата, [Дата]))
Теперь ваш запрос явно просматривает платежи только за год = 2010, однако я думаю, что вы хотели, чтобы ваши январь/февраль/март фактически представляли 2009 год. Если это так, вам нужно немного скорректировать это для этого случая. Не запрашивайте значения суммы для каждого столбца, а только условие разницы дат в месяцах. Поместите остальное в предложение WHERE.
ВЫБОР SUM(случай, когда DateDiff(m, PaymentDate, @start) = 0 затем Сумма еще 0 конец ) КАК "Апр", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 1 затем сумма еще 0 конец ) КАК "май", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 2 затем Сумма еще 0 конец ) КАК "Июнь", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 3 затем Сумма еще 0 конец ) КАК "июль", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 4 затем Сумма еще 0 конец ) КАК "Авг", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 5 затем Сумма еще 0 конец ) КАК "Сентябрь", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 6 затем Сумма еще 0 конец ) КАК "Октябрь", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 7 затем Сумма еще 0 конец ) AS "Nov", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 8 затем Сумма еще 0 end ) AS "Dec", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 9затем Сумма еще 0 конец ) КАК "Ян", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 10 затем Сумма еще 0 конец ) КАК "февраль", SUM(случай, когда DateDiff(m, PaymentDate, @start) = 11 затем Сумма еще 0 конец ) КАК "март" ОТ Платежи I ПРИСОЕДИНЯЙТЕСЬ к Live L на I. LiveID = L.Record_Key ГДЕ Год = 2010 И UserID = 100
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google
Зарегистрироваться через Facebook
Зарегистрируйтесь, используя адрес электронной почты и пароль
Опубликовать как гость
Электронная почта
Обязательно, но не отображается
Опубликовать как гость
Электронная почта
Требуется, но не отображается
Sql Group By Month Names — Примеры запросов
3 года назад
от Thomas Brown
2,418 Просмотров
Чтобы сгруппировать запросы по названию месяца, мы должны использовать функцию datename в sql. Эта функция получает два параметра. Первый — интервал; для названий дней мы должны писать dw, для названий месяцев мы должны писать m или mm. Второй параметр — это параметр даты. Если нам нужен только индекс месяца, мы также можем использовать функцию месяца.
Давайте сделаем несколько примеров с базой данных библиотек
Примеры с базой данных библиотек
Пример 1 :Укажите количество студентов, родившихся каждый месяц.
Transact-SQL
Выберите DateName(мм,дата рождения) как MName,count(*) как Count от студентов
группировка по DATEName(мм,дата рождения)
Выберите DateName(мм,дата рождения) в качестве MName,количество(*) в качестве количества из студентов группировать по DATEName(мм,дата рождения) |
Результаты запроса
Пример 2 :Укажите число учащихся, рожденных каждый месяц, в порядке подсчета.
Transact-SQL
Выберите DateName(мм,дата рождения) как MName,count(*) как Count от студентов
группировка по DATEName(мм,дата рождения)
заказ по количеству
Выберите DateName(мм,дата рождения) в качестве MName,количество(*) в качестве количества из студентов группировать по DATEName(мм,дата рождения) заказ по количеству |
Результаты запроса
Пример 3: Перечислите количество студентов Mail, родившихся каждый месяц, в порядке количества.
Transact-SQL
Выберите DateName(мм,дата рождения) как MName,count(*) как Count от студентов
где пол = ‘М’
группировка по DATEName(мм,дата рождения)
заказ по количеству
Выберите DateName(мм,дата рождения) как MName,count(*) как Count от учащихся где пол = ‘М’ группа по ДАТАИмя(мм,дата рождения) порядок по количеству |
Результаты запроса
Пример 4: Список названий месяцев и количество взятых книг в этом месяце
Transact-SQL
Выберите DATENAME(mm,takenDate) как Месяцы,count(*) как Count from заимствования
сгруппировать по DATENAME(mm,takenDate)
Выберите DATENAME(mm,takenDate) как Months,count(*) как Count from заимствования группа по DATENAME(mm,takenDate) |
Результаты запроса
Пример 5: Список названий месяцев и количество взятых книг в этом месяце, упорядоченное по количеству книг
Transact-SQL
Выберите DATENAME(mm,takenDate) как Months,count(*) как BookCount из займов
сгруппировать по DATENAME(mm,takenDate)
заказ по BookCount
Выберите DATENAME(mm,takenDate) как Months,count(*) как BookCount из заимствований группа по DATENAME(mm,takenDate) заказ по BookCount |
или
Transact-SQL
Выберите DATENAME(mm,takenDate) как Months,count(*) как BookCount из займов
сгруппировать по DATENAME(mm,takenDate)
заказать по 2
Выберите DATENAME(mm,takenDate) как Месяцы, count(*) как BookCount из заимствований группировать по DATENAME(mm,takenDate) упорядочить по 2 |
Результаты запроса
Пример 6: Список названий месяцев 2011 года и количество взятых книг в этом месяце, упорядоченное по количеству книг
Transact-SQL
Выберите DATENAME(mm,takenDate) как Months,count(*) как BookCount из займов
где год (взятая дата) = 2015
сгруппировать по DATENAME(mm,takenDate)
заказ по BookCount
Выберите DATENAME(mm,takenDate) как Months,count(*) как BookCount из заимствований где year(takenDate) = 2015 группа по DATENAME(mm,takenDate) порядок по BookCount |
Результаты запроса
Пример 7: Перечислите названия месяцев 2011 года и количество взятых книг в этом месяце, упорядоченное по количеству книг.