SQL-запрос для подсчета нескольких строк с одним выходом

Question

У меня есть база данных, включающая в себя определенные строки, такие как {TICKER|IBM}, к которым я буду обращаться как строки тикеров. Моя цель - подсчитать количество строк тикера в день для нескольких строк.

Моя таблица базы данных "твиты" содержит строки "твит_ид", "созданные в" (дд / мм / гггг чч / мм / сс) и "обработанный текст". Строки тикера, такие как "{TICKER|IBM}", находятся в строке "обработанный текст".

На данный момент у меня есть рабочий SQL-запрос для подсчета одной строки тикера (благодаря помощи других Stackru-ers). Я хотел бы иметь SQL-запрос, в котором я могу сосчитать несколько строк (например, рядом с {TICKER|IBM}, а также {TICKER|GOOG} и {TICKER|BAC}).

Рабочий SQL-запрос для подсчета одной строки тикера выглядит следующим образом:

SELECT d.date, IFNULL(t.count, 0) AS tweet_count
FROM all_dates AS d
LEFT JOIN (
    SELECT COUNT(DISTINCT tweet_id) AS count, DATE(created_at) AS date
    FROM tweets
    WHERE processed_text LIKE '%{TICKER|IBM}%'
    GROUP BY date) AS t
ON d.date = t.date

Таким образом, конечный вывод должен содержать столбец с датой, столбец с {TICKER|IBM}, столбец с {TICKER|GOOG} и один с {TICKER|BAC}.

Мне было интересно, возможно ли это и есть ли у вас решение для этого? У меня есть более 100 различных строк. Конечно, делать их один за другим - вариант, но он очень трудоемкий.

Answer 1

Если я правильно понимаю, вы можете сделать это с помощью условного агрегирования:

SELECT d.date, coalesce(IBM, 0) as IBM, coalesce(GOOG, 0) as GOOG, coalesce(BAC, 0) AS BAC
FROM all_dates d LEFT JOIN
     (SELECT DATE(created_at) AS date,
             COUNT(DISTINCT CASE WHEN processed_text LIKE '%{TICKER|IBM}%' then tweet_id
                   END) as IBM,
             COUNT(DISTINCT CASE WHEN processed_text LIKE '%{TICKER|GOOG}%' then tweet_id
                   END) as GOOG,
             COUNT(DISTINCT CASE WHEN processed_text LIKE '%{TICKER|BAC}%' then tweet_id
                   END) as BAC
      FROM tweets
      GROUP BY date
     ) t
     ON d.date = t.date;

Answer 2

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

   SELECT d.date
        , IFNULL(SUM(t.processed_text LIKE '%{TICKER|IBM}%' ),0) AS `cnt_ibm`
        , IFNULL(SUM(t.processed_text LIKE '%{TICKER|GOOG}%'),0) AS `cnt_goog`
        , IFNULL(SUM(t.processed_text LIKE '%{TICKER|BAC}%' ),0) AS `cnt_goog`
        , IFNULL(SUM(t.processed_text LIKE '%{TICKER|...}%' ),0) AS `cnt_...`
     FROM all_dates d
     LEFT
     JOIN tweets t
       ON t.created_at >= d.date
      AND t.created_at < d.date + INTERVAL 1 DAY
    GROUP BY d.date

ЗАМЕЧАНИЯ: выражения внутри SUM Приведенные выше агрегаты оцениваются как логические значения, поэтому они возвращают 1 (если true), 0 (если false) или NULL. Я бы избегал оборачивать created_at столбец в функции DATE() и используйте вместо этого сканирование диапазона, особенно если добавлен предикат (WHERE clause) that restricts the values ofДатаbeing returned fromall_dates`.

В качестве альтернативы, выражения вроде этого будут возвращать эквивалентный результат:

     , SUM(IF(t.process_text LIKE '%{TICKER|IBM}%' ,1,0)) AS `cnt_ibm`