SQL-запрос для вызова нескольких столбцов с условиями (с NA)

Это постоянный вопрос из моего предыдущего вопроса < / а>. Я хочу написать SQL-запрос, вызывающий несколько столбцов с условиями. Я работаю над R Studio с использованием пакета RMySQL. Мой сервер MySQL.

Таблица выглядит так.

organisation          A           B           C           D
Ikea         2018-04-01  2018-05-07  2018-05-09  2018-05-01
Ikea         2018-06-01  2018-05-03  2018-05-29          NA   
Orange       2018-04-02  2018-05-01  2018-07-08  2018-05-26 
Ikea         2018-06-02  2018-05-01          NA  2018-05-26
Nestle       2018-06-02  2018-05-01          NA  2018-05-26
Ikea                 NA  2018-05-05  2018-04-02  2018-06-01

И я хочу получить строку, в которой организация - это Ikea, а самая ранняя дата среди четырех столбцов (A, B, C, D) находится между 01.05.2018 и 31.05.2018.

В строке, содержащей значения NA, я хочу проигнорировать NA и посмотреть, какая самая ранняя дата среди остальных значений. Например, для второй строки самая ранняя дата - «2018-05-03» (столбец B), поэтому она соответствует критериям.

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

organisation          A           B           C           D
Ikea         2018-06-01  2018-05-03  2018-05-29          NA    
Ikea         2018-06-02  2018-05-01          NA  2018-05-26

Как мне написать SQL-запрос? Вот моя попытка после получения ответа на мой предыдущий вопрос, но она не работает для строк с NA.

SELECT * FROM myTable 
WHERE organisation LIKE Ikea
LEAST(A, B, C, D) >= '2018-05-01' AND
LEAST(A, B, C, D) < '2018-06-01'

Спасибо за любую помощь!


mysql r sql conditional-statements rmysql
person Makoto Miyazaki    schedule 26.06.2018    source источник
comment
Часто я советовал аналитикам данных долго хранить данные в R, Pandas и т. Д., Но это абсолютно необходимо в таблицах базы данных для эффективности, масштабируемости и хранения. Нормализованные структурированные легче запрашивать. Если возможно, измените форму таблицы с помощью одного столбца значений даты, одного столбца буквенного индикатора и одного столбца организации. Тогда агрегация проста с min и max.   -  person Parfait    schedule 26.06.2018
comment
Спасибо за совет! Из-за иерархии в моей компании было немного сложно изменить форму таблицы так, как вы упомянули. Но на самом деле это было бы лучшим решением, если бы я мог это сделать.   -  person Makoto Miyazaki    schedule 27.06.2018

Ответы (3)


arrow_upward
0
arrow_downward

Просто используйте coalesce():

SELECT *
FROM myTable 
WHERE organisation LIKE 'Ikea' AND
      LEAST(COALESCE(A, '2019-01-01'), COALESCE(B, '2019-01-01'), COALESCE(C, '2019-01-01'), COALESCE(D, '2019-01-01')) >= '2018-05-01' AND
      LEAST(COALESCE(A, '2019-01-01'), COALESCE(B, '2019-01-01'), COALESCE(C, '2019-01-01'), COALESCE(D, '2019-01-01')) < '2018-06-01';

'2019-01-01' - произвольная дата после диапазона.

person Gordon Linoff    schedule 26.06.2018

arrow_upward
0
arrow_downward

вы можете попробовать использовать IFNULL() для значений и использовать getdate(), поэтому, если бы он был равен нулю, он просто использовал бы сегодня, и тогда это не будет считаться наименьшим, что в основном их игнорирует.

например: LEAST(IFNULL(A,getdate()), IFNULL(B,getdate()), IFNULL(C,getdate()), IFNULL(D,getdate()))

надеюсь, это может подтолкнуть вас в правильном направлении

если NA не является строкой, в которой вы можете использовать оператор case, например case when A = 'NA' THEN getdate() end

может в конечном итоге выглядеть примерно так

SELECT * FROM myTable 
WHERE organisation LIKE Ikea
LEAST(IFNULL(A,getdate()), IFNULL(B,getdate()), IFNULL(C,getdate()), IFNULL(D,getdate())) >= '2018-05-01' AND
LEAST(IFNULL(A,getdate()), IFNULL(B,getdate()), IFNULL(C,getdate()), IFNULL(D,getdate())) < '2018-06-01'

or

SELECT * FROM myTable 
WHERE organisation LIKE Ikea
LEAST(case when A = 'NA' THEN getdate() else A end, case when B = 'NA' THEN getdate() else B end, case when C = 'NA' THEN getdate() else C end, case when D = 'NA' THEN getdate() else D end) >= '2018-05-01' AND
LEAST(case when A = 'NA' THEN getdate() else A end, case when B = 'NA' THEN getdate() else B end, case when C = 'NA' THEN getdate() else C end, case when D = 'NA' THEN getdate() else D end) < '2018-06-01'
person t..    schedule 26.06.2018

arrow_upward
0
arrow_downward

Рассмотрите возможность присоединения к агрегатному запросу, полученному с использованием UNION ALL:

SELECT m.*
FROM myTable m
INNER JOIN 
   (SELECT t.ID, MIN(t.`DateValue`) As MinDate
    FROM 
       (SELECT ID, organisation, 'A' As 'Category', A As `DateValue` FROM myTable
        UNION ALL
        SELECT ID, organisation, 'B' As 'Category', B As `DateValue` FROM myTable
        UNION ALL
        SELECT ID, organisation, 'C' As 'Category', C As `DateValue` FROM myTable
        UNION ALL
        SELECT ID, organisation, 'D' As 'Category', D As `DateValue` FROM myTable) As t
    WHERE t.organisation = 'Ikea'
    GROUP BY t.ID
    HAVING MIN(t.`DateValue`) >= '2018-05-01' AND MIN(t.`DateValue`) < '2018-06-01') As agg
ON m.ID = agg.ID;

RexTester Demo (Примечание: даты указаны в формате ДД-ММ-ГГГГ)

Результаты SQL-запроса

person Parfait    schedule 26.06.2018