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

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

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

На данный момент у меня есть рабочий SQL-запрос для подсчета одной строки тикера (благодаря помощи других пользователей Stackoverflow). Я хотел бы иметь 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 различных строк тикера. Конечно, делать их по одному — вариант, но это очень трудоемкий процесс.


mysql sql string count ticker
person Geoffrey    schedule 30.06.2014    source источник
comment
Термин, который вы ищете, — это «стержень» (превращение нескольких строк в нескольких столбцах в одну строку со многими столбцами). Вы сможете найти множество вариантов его использования в Stackoverflow с помощью поиска или двух.   -  person Twelfth    schedule 01.07.2014
comment
Это немного сложно, но я считаю, что для того, чтобы обобщить этот запрос для всех символов тикера, вам нужно создать запрос сводной таблицы. Кроме того, используя подстановочные знаки, например. '%{TICKER|IBM}%' приведет к последовательному чтению твитов таблицы и ухудшит производительность. В этом случае я думаю, вы можете использовать LEFT(processed_text, Length('{TICKER|')) = '{TICKER|' чтобы найти тикеры. Это SO-пример динамических сводных таблиц в mysql: динамические столбцы"> stackoverflow.com/questions/12598120/   -  person ron tornambe    schedule 01.07.2014

Ответы (2)


arrow_upward
1
arrow_downward

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

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;
person Gordon Linoff    schedule 30.06.2014
comment
Спасибо Гордон, это работает отлично! Ввод названий компаний в скрипте занимает намного меньше времени, чем ввод всех 100+ тикерных строк вручную, так что я очень ценю это!! - person Geoffrey; 01.07.2014

arrow_upward
0
arrow_downward

Я бы вернул указанный набор результатов следующим образом, добавив выражения в список 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 (если истина), 0 (если ложь) или NULL. Я бы не стал оборачивать столбец created_at в функцию DATE() и вместо этого использовал сканирование диапазона, особенно если добавлен предикат (WHERE clause) that restricts the values ofdatebeing returned fromall_dates`.

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

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