Допустим, у меня есть таблица wp_users со следующими данными:
Name user_registered ID dog 2008-05-14 18:53:30 1 cat 2008-05-14 12:13:20 2 mouse 2008-05-14 08:51:32 3 giraffe 2008-05-15 22:13:31 4 Moose 2008-05-16 13:20:30 5 monkey 2008-05-16 08:51:32 6 mongoose 2008-05-16 22:13:31 7 fish 2008-05-16 13:00:30 8
Затем у меня есть другая таблица wp_usermeta , где wp_users.ID == wp_usermeta.user_id :
user_id meta_key meta_value 1 wp_capabilities a:1:{s:10:"subscriber";s:1:"1";} 2 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";} 2 wp_s2member_subscr_id I-SDJKSDD 3 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";} 3 wp_s2member_subscr_id I-sdfsdfsdf 4 wp_cabilities a:1:{s:10:"subscriber";s:1:"1";} 5 wp_cabilities a:1:{s:10:"subscriber";s:1:"1";} 6 wp_cabilities a:1:{s:10:"subscriber";s:1:"1";} 7 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";} 7 wp_s2member_subscr_id I-sd45gds 8 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";} 8 wp_s2member_subscr_id I-3fskkhh
То, что я ищу, – это способ генерации некоторых сводных столбцов, которые подсчитывают количество подписчиков подписчиков и подписчиков s2member_level2 за каждый день wp_users.user_registered date (меня не интересует час дня, только дата). Чтобы определить их немного лучше:
« subscriber »: не будет строки, где meta_key = wp_s2member_subscr_id – ее просто не существует. Обратите внимание, что пользователи 1,4,5 и 6 не имеют этой строки, поэтому они являются «подписчиками». Также в строке « wp_capabilities » у них будет слово « subscriber » вместо « s2member_level2 ». Оба критерия НЕ должны выполняться, потому что они оба верны для всех подписчиков (я упоминаю оба варианта, если один легче проверить, чем другой).
« s2member_level2 »: У нас есть строка, где meta_key = wp_s2member_subscr_id с некоторыми значениями случайных букв (пользователи 2,3,7,8 в таблицах выше). Он также будет иметь слово « s2member_level2 » в значении для meta_value . Оба критерия НЕ должны выполняться, потому что они оба верны для всех s2member_level2 (я упоминаю оба варианта, если один легче проверить, чем другой).
Тогда идеальный вывод будет выглядеть так, как показано в приведенных выше примерах:
Date subscriber s2member_level2 2008-05-14 1 2 2008-05-15 1 0 2008-05-16 2 2
Это более сложный вопрос о найденном здесь вопросе (и ответе), который просто искал общее количество подписчиков. Это предполагает различие между подписчиками и s2member_level2. Я предполагаю, что это может быть сделано с использованием некоторых причудливых заявлений о присоединении и будет признательна за любую помощь!
select date, SUM(case when subscriber = 1 and s2member_level2 = 0 then 1 else 0 end) subscriber, SUM(case when s2member_level2 = 1 then 1 else 0 end) s2member_level2 from ( select date(user_registered) date, id, sum(case when B.meta_key = "wp_capabilities" then 1 else 0 end) as subscriber, sum(case when B.meta_key = "wp_s2member_subscr_id" then 1 else 0 end) as s2member_level2 from wp_users A LEFT JOIN wp_usermeta B ON A.Id = B.user_id group by date(user_registered), id ) C group by date
Это эффективное решение, использующее временную таблицу.
Прежде всего, получите строку для каждого пользователя и соедините все их «мета-ключи», а также отбросьте временную часть из штампа даты и времени:
CREATE TEMPORARY TABLE user_keys SELECT wp_users.ID, DATE_FORMAT(user_registered,'%Y-%m-%d') AS user_registered GROUP_CONCAT(meta_key) AS meta_keys FROM wp_users LEFT JOIN wp_usermeta AS subs ON (wp_users.ID = subs.user_id) GROUP BY wp_users.ID ORDER BY wp_users.ID
Затем SELECT на вашей временной таблице, чтобы проверить, какие ключи у каждого пользователя есть, и подсчитайте их:
SELECT user_registered, SUM(IF(FIND_IN_SET('wp_s2member_subscr_id',meta_keys) = 0, 1,0)) AS subscriber, SUM(IF(FIND_IN_SET('wp_s2member_subscr_id',meta_keys) > 0, 1,0)) AS s2member_level2 FROM user_keys GROUP BY user_registered ORDER BY user_registered
Бьюсь об заклад, кто-то может придумать одно решение для запросов со всеми возможными подзапросами и еще много чего, но это работает с вашими данными примера …
если вы используете триггер и сводную таблицу, как было предложено в вашем предыдущем вопросе, ваши вставки будут немного медленнее, но вы сможете быстро запросить без агрегации мух:
tally / count mysql результаты за день