Выбор с помощью сводной таблицы в SQL Server
Я перенес свою базу данных MS Access на SQL Server. У меня есть сводный запрос, который я не могу адаптировать.
В MS Access это:
TRANSFORM FIRST(eff.efi) AS efici
SELECT eff.semana_ano, FIRST(eff.data), LAST(eff.data), ROUND(Avg(eff.efi), 2) AS media_semana
FROM
(SELECT dia_semana, semana_ano, efi
FROM tmp_lista) AS eff
GROUP BY eff.semana_ano
PIVOT eff.ndia_semana
Данные в SQL Server:
CREATE TABLE tmp_lista
(
dia date,
dia_semana varchar(5),
semana_ano int,
efi decimal(5,2)
);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-01', 'D5', 5, 0.98);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-02', 'D6', 5, 0.5);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-03', 'D7', 5, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-04', 'D1', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-05', 'D2', 6, 0.49);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-06', 'D3', 6, 0.65);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-07', 'D4', 6, 0.3);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-08', 'D5', 6, 1.18);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-09', 'D6', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-10', 'D7', 6, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-11', 'D1', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-12', 'D2', 7, 0.57);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-13', 'D3', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-14', 'D4', 7, 0.51);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-15', 'D5', 7, 0.65);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-16', 'D6', 7, 0.49);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-17', 'D7', 7, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-18', 'D1', 8, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-19', 'D2', 8, 0.34);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-20', 'D3', 8, 0.46);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-21', 'D4', 8, 0.43);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-22', 'D5', 8, 0.32);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-23', 'D6', 8, 0.62);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-24', 'D7', 8, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-25', 'D1', 9, NULL);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-26', 'D2', 9, 0.62);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-27', 'D3', 9, 0.44);
INSERT INTO tmp_lista (dia, dia_semana, semana_ano, efi) VALUES ('2024-02-28', 'D4', 9, 0.54);
dia |dia_semana|semana_ano|efi |
----------|----------|----------|------|
2024-02-01|D5 | 5| 0.98|
2024-02-02|D6 | 5| 0.50|
2024-02-03|D7 | 5|[NULL]|
2024-02-04|D1 | 6|[NULL]|
2024-02-05|D2 | 6| 0.49|
2024-02-06|D3 | 6| 0.65|
2024-02-07|D4 | 6| 0.30|
2024-02-08|D5 | 6| 1.18|
2024-02-09|D6 | 6|[NULL]|
2024-02-10|D7 | 6|[NULL]|
2024-02-11|D1 | 7|[NULL]|
2024-02-12|D2 | 7| 0.57|
2024-02-13|D3 | 7|[NULL]|
2024-02-14|D4 | 7| 0.51|
2024-02-15|D5 | 7| 0.65|
2024-02-16|D6 | 7| 0.49|
2024-02-17|D7 | 7|[NULL]|
2024-02-18|D1 | 8|[NULL]|
2024-02-19|D2 | 8| 0.34|
2024-02-20|D3 | 8| 0.46|
2024-02-21|D4 | 8| 0.43|
2024-02-22|D5 | 8| 0.32|
2024-02-23|D6 | 8| 0.62|
2024-02-24|D7 | 8|[NULL]|
2024-02-25|D1 | 9|[NULL]|
2024-02-26|D2 | 9| 0.62|
2024-02-27|D3 | 9| 0.44|
2024-02-28|D4 | 9| 0.54|
Мне удалось адаптироваться к этому моменту:
SELECT
semana_ano, D1, D2, D3, D4, D5, D6, D7
FROM
(SELECT dia_semana, semana_ano, efi FROM tmp_lista) AS dp
PIVOT
(SUM(efi) FOR dia_semana IN (D1, D2, D3, D4, D5, D6, D7)) result_dp;
semana_ano|D1 |D2 |D3 |D4 |D5 |D6 |D7 |
----------|------|------|------|------|------|------|------|
5 |[NULL]|[NULL]|[NULL]|[NULL]| 0.98| 0.50|[NULL]|
6 |[NULL]| 0.49| 0.65| 0.30| 1.18|[NULL]|[NULL]|
7 |[NULL]| 0.57|[NULL]| 0.51| 0.65| 0.49|[NULL]|
8 |[NULL]| 0.34| 0.46| 0.43| 0.32| 0.62|[NULL]|
9 |[NULL]| 0.62| 0.44| 0.54|[NULL]|[NULL]|[NULL]|
Вам также понадобится столбец для минимального дня недели, столбец с максимальным днем недели и столбец со средним значением за 7 дней.
Что-то вроде:
semana_ano|D1 |D2 |D3 |D4 |D5 |D6 |D7 |min(dia) |max(dia) |avg(7 dias)|
----------|------|------|------|------|------|------|------|----------|----------|-----------|
5 |[NULL]|[NULL]|[NULL]|[NULL]| 0.98| 0.50|[NULL]|2024-02-01|2024-02-03| 0.74|
6 |[NULL]| 0.49| 0.65| 0.30| 1.18|[NULL]|[NULL]|2024-02-04|2024-02-10| 0.66|
7 |[NULL]| 0.57|[NULL]| 0.51| 0.65| 0.49|[NULL]|2024-02-11|2024-02-17| 0.56|
8 |[NULL]| 0.34| 0.46| 0.43| 0.32| 0.62|[NULL]|2024-02-18|2024-02-24| 0.43|
9 |[NULL]| 0.62| 0.44| 0.54|[NULL]|[NULL]|[NULL]|2024-02-25|2024-02-28| 0.53|
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
52
1
Перейти к ответу
Данный вопрос помечен как решенный
Ответы 1
Ответ принят как подходящий
PIVOT недостаточно гибок для выполнения нескольких типов агрегирования. Как предположил @siggemannen, условная агрегация — это билет. Например.:
SELECT semana_ano,
D1 = NULLIF(SUM(CASE dia_semana WHEN 'D1' THEN efi ELSE 0 END),0),
D2 = NULLIF(SUM(CASE dia_semana WHEN 'D2' THEN efi ELSE 0 END),0),
D3 = NULLIF(SUM(CASE dia_semana WHEN 'D3' THEN efi ELSE 0 END),0),
D4 = NULLIF(SUM(CASE dia_semana WHEN 'D4' THEN efi ELSE 0 END),0),
D5 = NULLIF(SUM(CASE dia_semana WHEN 'D5' THEN efi ELSE 0 END),0),
D6 = NULLIF(SUM(CASE dia_semana WHEN 'D6' THEN efi ELSE 0 END),0),
D7 = NULLIF(SUM(CASE dia_semana WHEN 'D7' THEN efi ELSE 0 END),0),
[min(dia)] = MIN(dia),
[max(dia)] = MAX(dia),
[avg(7 dias)] = AVG(efi)
FROM tmp_lista
GROUP BY semana_ano;
Другие вопросы по теме
Как получить 10 различных значений последних записей?
Динамическое создание пользователей из табличной переменной
Вернуть повторяющиеся строки из таблицы
Ищем способ избежать курсора
SQL Server устанавливает формат по умолчанию при отображении столбцов
Вставка встроенного запроса SQL
Использование зарезервированного ключевого слова MERGE в разных версиях SQL Server
Получение только одной строки данных для каждого первичного ключа, когда существует множество внешних ключей, равных ему
Попытка сохранить типы данных при динамическом дублировании таблицы
Sp_executesql в цикле, выполнение которого занимает ВЕЧНОСТЬ. Замедление во время бега. Утечка памяти?
Похожие вопросы
Как сгенерировать счетчик 0 в группе с несколькими столбцами с помощью count (*)
Динамическое создание пользователей из табличной переменной
Разберите SQL-запросы Google BQ и получите все ссылки на таблицы
Множественный случай и условия
Запрос MYSQL для извлечения данных по группам
Как найти наибольшее количество последовательных нулей в столбце MySQL?
Конкатенация строк Sql всегда плоха?
Вернуть повторяющиеся строки из таблицы
Как оптимизировать запросы по комбинированным критериям в большой базе данных SQL?
Почему порядок меняется при использовании UNION ALL в PostgreSQL?
используйте условную агрегацию вместо сводной, чтобы получить больше гибкости, множество ответов о том, как это сделать, здесь