Оператор SELECT для удаления пустых столбцов из набора результатов с переменными данными

У меня есть таблица, импортированная из 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.

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

См. также:  Symfony 4 - Ошибка открытия требуется getSwiftmailer_EmailSender_ListenerService

Невозможно сделать столбцы динамическими, только строки.   —  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

Понравилась статья? Поделиться с друзьями:
IT Шеф
Комментарии: 2
  1. Metafaniel

    Вы можете идентифицировать пустые столбцы с помощью:

    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

  2. Metafaniel

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

    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;
    
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: