Оператор SELECT для удаления пустых столбцов из набора результатов с переменными данными [mysql, sql, dynamic-sql, select, sql-null]

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

/* Table Structure */
CREATE TABLE `imported_data` (
  `title` varchar(45) DEFAULT NULL,
  `description` varchar(45) DEFAULT NULL,
  `language` varchar(45) DEFAULT NULL,
  `url` varchar(45) DEFAULT NULL,
  `category` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

/* Table Data */
INSERT INTO imported_data (title, `description`, `language`, url, category) VALUES
('Le Titre', '', 'fra', '', '1'),
('', 'English Letters for Kids', '', 'https://anything.net', '2'),
('', '', '', '', ''),
('Master', 'The greatest master.', 'eng', 'https://www.master.com', '3'),
('', '', '', '', ''),
('', '', 'spa', '', '');


╔══════════╤══════════════════════════╤══════════╤═══════════════════════╤══════════╗
║ title    │ description              │ language │ url                   │ category ║
╠══════════╪══════════════════════════╪══════════╪═══════════════════════╪══════════╣
║          │                          │ fra      │                       │ 1        ║
╟──────────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║          │ English letters for Kids │          │ https://anything.net  │ 2        ║
╟──────────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║          │                          │          │                       │          ║
╟──────────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║          │ The greatest master.     │ eng      │ https://www.master.com │ 3        ║
╟──────────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║          │                          │          │                       │          ║
╟──────────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║          │                          │ spa      │                       │          ║
╚══════════╧══════════════════════════╧══════════╧═══════════════════════╧══════════╝

ПРИМЕЧАНИЕ. Учтите, что приведенная выше таблица / данные поступают из внешнего источника и не подлежат изменению, у них также нет первичного ключа. Он просто импортируется, как в MySQL. Я не получу NULL значений, так как все столбцы VARCHAR.

Я использую следующий оператор SELECT, чтобы получить эту же таблицу, но без полных пустых строк:

SELECT title, `description`, `language`, url, category
FROM imported_data2 WHERE
title != ''  OR `description` != ''  OR `language` != ''  OR url != ''  OR category != '' 

╔═══════╤══════════════════════════╤══════════╤═══════════════════════╤══════════╗
║ title │ description              │ language │ url                   │ category ║
╠═══════╪══════════════════════════╪══════════╪═══════════════════════╪══════════╣
║       │                          │ fra      │                       │ 1        ║
╟───────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║       │ English letters for Kids │          │ https://anything.net  │ 2        ║
╟───────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║       │ The greatest master.     │ eng      │ https://www.master.com │ 3        ║
╟───────┼──────────────────────────┼──────────┼───────────────────────┼──────────╢
║       │                          │ spa      │                       │          ║
╚═══════╧══════════════════════════╧══════════╧═══════════════════════╧══════════╝

Что мне нужно сделать, чтобы удалить столбцы, все строки которых пусты? В этом примере мне нужна таблица, как указано выше, но без пустого столбца title.

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

См. также: Как использовать clang с заголовками mingw-w64 в Windows

Невозможно сделать столбцы динамическими, только строки. — person Metafaniel schedule 13.10.2020

Если вы не пишете динамический SQL в хранимой процедуре. — person Metafaniel schedule 13.10.2020

Рассмотрите возможность обработки проблем с отображением данных (если это так) в коде приложения. — person Metafaniel schedule 13.10.2020

Обычно обрабатывается в коде постфактум, или вы можете создать временную таблицу с результатами и удалить столбцы из временной таблицы, а затем выбрать * из временной таблицы. — person Metafaniel schedule 13.10.2020

Post Views: 110

set @sql = null; select concat_ws(', ', case when count(nullif(title, '')) > 0 then 'title' end, case when count(nullif(description, '')) > 0 then 'description' end, case when count(nullif(language, '')) > 0 then 'language' end, case when count(nullif(url, '')) > 0 then 'url' end, case when count(nullif(category, '')) > 0 then 'category' end ) into @sql from imported_data; set @sql = concat('select ', @sql, ' from imported_data'); prepare stmt from @sql; execute stmt; deallocate prepare stmt;

set @sql = null; select concat_ws(', ', case when count(nullif(title, '')) > 0 then 'title' end, case when count(nullif(description, '')) > 0 then 'description' end, case when count(nullif(language, '')) > 0 then 'language' end, case when count(nullif(url, '')) > 0 then 'url' end, case when count(nullif(category, '')) > 0 then 'category' end ) into @sql from imported_data; set @sql = concat('select ', @sql, ' from imported_data where', ( SELECT INSERT( GROUP_CONCAT('OR `', `COLUMN_NAME`, '` != \'\' ' SEPARATOR ' '), 1, 3, '') FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'mydb' AND `TABLE_NAME` = 'imported_data' ) ); prepare stmt from @sql; execute stmt; deallocate prepare stmt;

Комментарии: 2

Metafaniel 13.10.2020 в 00:00
Вы можете идентифицировать пустые столбцы с помощью:
```
select 
    count(title), 
    count(description),
    count(language),
    count(url),
    count(cagegory)
from imported_data
```
Столбцы, которые возвращаются со счетом 0, содержат только null значения. Затем вы можете удалить связанные столбцы, если хотите.

Я не получу значения NULL, так как все столбцы — VARCHAR.

Строковый столбец обязательно может содержать null значений. Если вы действительно имеете в виду пустую строку, а не null, используйте nullif():
```
select 
    count(nullif(title, '')), 
    count(nullif(description, '')),
    count(nullif(language, '')),
    count(nullif(url, '')),
    count(nullif(category, ''))
from imported_data
```
Если вам нужно что-то полностью динамическое, подумайте о создании, используя подготовленный оператор:
```
set @sql = null;

select concat_ws(', ',
    case when count(nullif(title, ''))       > 0 then 'title'       end,
    case when count(nullif(description, '')) > 0 then 'description' end,
    case when count(nullif(language, ''))    > 0 then 'language'    end,
    case when count(nullif(url, ''))         > 0 then 'url'         end,
    case when count(nullif(category, ''))    > 0 then 'category'    end
) into @sql
from imported_data;

set @sql = concat('select ', @sql, ' from imported_data');  
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
```
Как бы он оставил столбец вне SELECT результата, если он был бы пустым? — person Metafaniel; 13.10.2020

Ваш второй запрос подсчитывает пустые значения, а не непустые значения. Таким образом, он не вернет 0, если столбец содержит только пустые значения, он будет содержать общее количество строк. — person Metafaniel; 13.10.2020

@Barmar: второй запрос возвращает 0 для столбцов, содержащих только пустые строки (или nulls). Что касается решения самой проблемы, см. Мое обновление, которое предлагает решение на основе динамического SQL. — person Metafaniel; 14.10.2020

Ваше подготовленное заявление, безусловно, лучший вариант. Я тоже пробовал нечто подобное. Единственная проблема с вашим текущим решением заключается в том, что остаются полные пустые строки (без пустых столбцов). Что осталось для этого? Спасибо!! — person Metafaniel; 14.10.2020

@GMB Я отправил себе ответ, основанный на вашем, с возможностью. Если вы считаете, что есть способ лучше, отредактируйте ответ, чтобы учесть вашу идею. Спасибо еще раз!! — person Metafaniel; 14.10.2020
Ответить

Metafaniel 13.10.2020 в 00:00

Используя ответ @GMB в качестве основы, я добавил к подготовленному оператору динамический способ проверки, являются ли какие-либо из проверенных полей пустыми в предложении where, таким образом вырезая полные пустые строки, например:

Ответить

Добавить комментарий