Итак, у меня есть база данных, которая подключена к 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 |
Я хочу:
Вывод в соответствии с таблицей выше должен выглядеть следующим образом:
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
Я новичок в sql и думаю, что они достаточно похожи, чтобы оправдать этот тег. Если это неправильно/противоречит правилам, я это отредактирую.
@Xyon4 обновите ожидаемый результат
Да, пожалуйста, удалите тег MySQL, спасибо. По аналогии, это все равно, что пометить вопрос о Lamborghini тегом Ferrari, потому что они оба являются чертовски похожими марками суперкаров, но я уверен, что вы можете себе представить, что это не понравится ;)
@Xyon4 Как идентификатор формата ABCDEF0123456789
меняется на 4-значный формат, например 1462
? Какая логика стоит за этим? Если нет, исправьте выходные данные в соответствии с входными или наоборот...
@ArtBindu Четырехзначные числа — это количество различных идентификаторов, найденных за каждый месяц, а не сами идентификаторы.
каков ваш результат в соответствии с таблицей ввода? Сначала отправьте свой вопрос правильно @Xyon4.
Спасибо за предложение. Я отредактировал образец таблицы и вывел
Спасибо. Помните: когда вы публикуете любой вопрос, необходимо предоставить как можно больше информации, чтобы другие могли легко и быстро понять вашу проблему.
SELECT DATEADD(month, DATEDIFF(month, 0, YourDateColumn), 0)
AS FirstDayOfMonth,
ID
FROM <TableName>;
Я попробовал ваш запрос и получил сообщение «Ошибка преобразования данных при преобразовании «INTEGER в TIMESTAMP»;». Может ли этот ответ быть для sql-сервера?
попробуйте cast или Convert() в запросе, скажем, запрос: SELECT cast(DATEADD(month, DATEDIFF(month, 0, YourDateColumn), 0)) AS FirstDayOfMonth, ID FROM <TableName>;
Давайте, это мой образец данных:
Вы хотите подсчитать количество разных идентификаторов за каждый месяц. Поскольку есть поле даты, вам нужно сгруппировать его по году и месяцу.
Попробуйте этот запрос, он должен работать для вас:
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
Спасибо! Запрос работает нормально, и благодаря вам я узнал кое-что новое. Теперь мне просто нужно проверить эффективность этого, но выглядит все нормально. Большое спасибо.
Я немного изменил это, и оно прекрасно работает даже с большими объемами данных. Как мне теперь написать запрос, который делает то же самое, но группирует по первому дню недели? Стоит ли задавать отдельный вопрос?
Используйте функцию MySQL: DAYNAME(data_rec)
, которая преобразует дату в day_name, а затем проверяет в соответствии с вашим условием.
Почему вы пометили это как H2, так и MySQL?