Как я могу сгруппировать даты по первому числу месяца, подсчитывая значения для каждого месяца?

Итак, у меня есть база данных, которая подключена к Java-программе с использованием встроенного H2. В нем у меня есть таблица ASSEGNAZIONI_IN, которая для этой цели содержит столбец DATA и столбец ID соответственно типов DATE и VARCHAR(16).

Таблица выглядит примерно так:

| DATA       | ID               |
|------------|------------------|
| 2023-03-02 | ABCDEF0123456789 |
| 2023-03-03 | DBFECA0123456799 |
| 2023-03-03 | ACFBDE0123456789 |
| 2023-03-04 | FEDCBA0123456789 |
| 2023-04-01 | FJWANN0124781248 |
| 2023-04-05 | ASFHWE1240912475 |
| 2023-05-16 | AQWRJG0124781248 |
| 2023-05-24 | AWPOAW1240912475 |
| 2023-05-16 | ASALFW0124781248 |
| 2023-10-20 | BBBBBB1231421412 |
| 2023-10-21 | CCCCCC4360282034 |

Я хочу:

  1. Чтобы получить первый день месяца вместе с количеством различных идентификаторов в этом месяце
  2. Чтобы отсортировать полученные дни по времени, по возрастанию, если это имеет значение.

Вывод в соответствии с таблицей выше должен выглядеть следующим образом:

2023-03-01  4
2023-04-01  2
2023-05-01  3
2023-10-01  2

Я использовал этот запрос, чтобы попытаться сделать это:

SELECT
    CONCAT(
        YEAR(ASSEGNAZIONI_IN.DATA),
        '-',
        MONTH(ASSEGNAZIONI_IN.DATA)
    ) AS MESE,
    COUNT(DISTINCT ASSEGNAZIONI_IN.ID)
FROM
    ASSEGNAZIONI_IN 
GROUP BY
    MESE

Но я получаю следующее:

2023-10     2
2023-3      4
2023-4      2
2023-5      3

Порядок явно нарушен, поскольку он сортируется по строке в алфавитном порядке, и меня не устраивает форматирование дат, поскольку я хотел бы получить первый день месяца вместо самого месяца.

Я также пробовал использовать функцию CONVERT(), но безрезультатно.

Любые предложения/советы приветствуются.

Обновлено: Спасибо @ArtBindu за помощь, последний запрос, который я использовал, был:

SELECT 
    CAST(
        CONCAT(
            YEAR(DATA),
            '-',
            MONTH(DATA),
            '-01') AS DATE) AS FIRSTOFMONTH,
    COUNT(DISTINCT ID)
FROM
    ASSEGNAZIONI_IN
GROUP BY
    FIRSTOFMONTH
ORDER BY
    FIRSTOFMONTH ASC

Почему вы пометили это как H2, так и MySQL?

Dai 07.06.2024 11:13

Я новичок в sql и думаю, что они достаточно похожи, чтобы оправдать этот тег. Если это неправильно/противоречит правилам, я это отредактирую.

Xyon4 07.06.2024 11:15

@Xyon4 обновите ожидаемый результат

Art Bindu 07.06.2024 11:16

Да, пожалуйста, удалите тег MySQL, спасибо. По аналогии, это все равно, что пометить вопрос о Lamborghini тегом Ferrari, потому что они оба являются чертовски похожими марками суперкаров, но я уверен, что вы можете себе представить, что это не понравится ;)

Dai 07.06.2024 11:17

@Xyon4 Как идентификатор формата ABCDEF0123456789 меняется на 4-значный формат, например 1462? Какая логика стоит за этим? Если нет, исправьте выходные данные в соответствии с входными или наоборот...

Art Bindu 07.06.2024 11:23

@ArtBindu Четырехзначные числа — это количество различных идентификаторов, найденных за каждый месяц, а не сами идентификаторы.

Xyon4 07.06.2024 11:26

каков ваш результат в соответствии с таблицей ввода? Сначала отправьте свой вопрос правильно @Xyon4.

Art Bindu 07.06.2024 11:39

Спасибо за предложение. Я отредактировал образец таблицы и вывел

Xyon4 07.06.2024 11:47

Спасибо. Помните: когда вы публикуете любой вопрос, необходимо предоставить как можно больше информации, чтобы другие могли легко и быстро понять вашу проблему.

Art Bindu 07.06.2024 12:01
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
9
58
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

SELECT DATEADD(month, DATEDIFF(month, 0, YourDateColumn), 0) 
AS FirstDayOfMonth, 
ID
FROM <TableName>;

Я попробовал ваш запрос и получил сообщение «Ошибка преобразования данных при преобразовании «INTEGER в TIMESTAMP»;». Может ли этот ответ быть для sql-сервера?

Xyon4 07.06.2024 11:50

попробуйте cast или Convert() в запросе, скажем, запрос: SELECT cast(DATEADD(month, DATEDIFF(month, 0, YourDateColumn), 0)) AS FirstDayOfMonth, ID FROM <TableName>;

Amit Bhalla 07.06.2024 12:00
Ответ принят как подходящий

Давайте, это мой образец данных:

Вы хотите подсчитать количество разных идентификаторов за каждый месяц. Поскольку есть поле даты, вам нужно сгруппировать его по году и месяцу.

Попробуйте этот запрос, он должен работать для вас:

WITH T1 AS (
  SELECT *,
  year(data_rec) as year_val,
  month(data_rec) AS month_val,
  ROW_NUMBER() OVER (PARTITION BY year(data_rec), month(data_rec), ID ORDER BY ID) AS row_no
  FROM T
)
SELECT 
  -- year_val,
  -- month_val,
  cast(concat(year_val, '-', month_val, '-', '01') as date) as group_by_year_month,
  COUNT(row_no) as total_distinct_ids
  FROM T1
  where row_no = 1
  GROUP BY year_val, month_val

Выход:

Пример кода для тестирования: db<>fiddle

Спасибо! Запрос работает нормально, и благодаря вам я узнал кое-что новое. Теперь мне просто нужно проверить эффективность этого, но выглядит все нормально. Большое спасибо.

Xyon4 07.06.2024 12:10

Я немного изменил это, и оно прекрасно работает даже с большими объемами данных. Как мне теперь написать запрос, который делает то же самое, но группирует по первому дню недели? Стоит ли задавать отдельный вопрос?

Xyon4 07.06.2024 12:21

Используйте функцию MySQL: DAYNAME(data_rec), которая преобразует дату в day_name, а затем проверяет в соответствии с вашим условием.

Art Bindu 07.06.2024 12:33

Другие вопросы по теме