SQL (Vertica) - подсчитать количество пользователей, которые вернулись в приложение как минимум x дней за последние 7 дней.

Предположим, у меня есть мой table лайк:

uid  day_used_app   
---  -------------
1    2012-04-28      
1    2012-04-29        
1    2012-04-30        
2    2012-04-29       
2    2012-04-30 
2    2012-05-01       
2    2012-05-21        
2    2012-05-22

Предположим, мне нужно количество уникальных пользователей, которые вернулись в приложение как минимум за 2 дня за последние 7 дней (с 2012-05-03).

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

select count(distinct case when num_different_days_on_app >= 2
                           then uid else null end) as users_return_2_or_more_days

    from (
         select uid,
                count(distinct day_used_app) as num_different_days_on_app
             from table
         where day_used_app between current_date() - 7 and current_date()
         group by 1

        )

Это дает мне:

users_return_2_or_more_days
---------------------------
            2

У меня есть вопрос:

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

        date               users_return_2_or_more_days
      --------             ---------------------------
    2012-04-28                        2
    2012-04-29                        2 
    2012-04-30                        3           
    2012-05-01                        4     
    2012-05-02                        4       
    2012-05-03                        3
Источник

2 ответа

Это поможет?

WITH
-- your original input, don't use in "real" query ...
input(uid,day_used_app) AS (
          SELECT 1,DATE '2012-04-28'
UNION ALL SELECT 1,DATE '2012-04-29'
UNION ALL SELECT 1,DATE '2012-04-30'
UNION ALL SELECT 2,DATE '2012-04-29'
UNION ALL SELECT 2,DATE '2012-04-30'
UNION ALL SELECT 2,DATE '2012-05-01'
UNION ALL SELECT 2,DATE '2012-05-21'
UNION ALL SELECT 2,DATE '2012-05-22'
)
-- end of input, start "real" query here, replace ',' with 'WITH'
,
one_week_b4 AS (
  SELECT
    uid
  , day_used_app
  , day_used_app -7 AS day_used_1week_b4
  FROM input
)
SELECT
  one_week_b4.uid
, one_week_b4.day_used_app
, count(*) AS users_return_2_or_more_days
FROM one_week_b4
JOIN input
  ON input.day_used_app BETWEEN one_week_b4.day_used_1week_b4 AND one_week_b4.day_used_app
GROUP BY
  one_week_b4.uid
, one_week_b4.day_used_app
HAVING count(*) >= 2
ORDER BY 1;

Выход:

uid|day_used_app|users_return_2_or_more_days
  1|2012-04-29  |                          3
  1|2012-04-30  |                          5
  2|2012-04-29  |                          3
  2|2012-04-30  |                          5
  2|2012-05-01  |                          6
  2|2012-05-22  |                          2

Это помогает вашим потребностям?

Марко вменяемый...

Источник 
SELECT DISTINCT
    t1.day_used_app,
    (
        SELECT SUM(CASE WHEN t.num_visits >= 2 THEN 1 ELSE 0 END)
        FROM
        (
            SELECT uid,
                   COUNT(DISTINCT day_used_app) AS num_visits
            FROM table
            WHERE day_used_app BETWEEN t1.day_used_app - 7 AND t1.day_used_app
            GROUP BY uid
        ) t
   ) AS users_return_2_or_more_days
FROM table t1
Источник
Другие вопросы по тегам