BigQuery: Как объединить эскизы HLL в оконную функцию? (Посчитайте разные значения по скользящему окну)

Question

Пример соответствующей схемы таблицы:

+---------------------------+-------------------+
| activity_date - TIMESTAMP | user_id - STRING  |
+---------------------------+-------------------+
| 2017-02-22 17:36:08 UTC   | fake_id_i24385787 |
+---------------------------+-------------------+
| 2017-02-22 04:27:08 UTC   | fake_id_234885747 |
+---------------------------+-------------------+
| 2017-02-22 08:36:08 UTC   | fake_id_i24385787 |
+---------------------------+-------------------+

Мне нужно подсчитывать активных отдельных пользователей по большому набору данных в течение скользящего периода времени (90 дней), и у меня возникают проблемы из-за размера набора данных.

Сначала я попытался использовать оконную функцию, подобную ответу здесь. /questions/32211274/bigquery-sql-dlya-28-dnevnogo-skolzyaschego-okna-bez-napisaniya-28-strok-sql/32211288#32211288

WITH
  daily AS (
  SELECT
    DATE(activity_date) day,
    user_id
  FROM
    `fake-table`)
SELECT
  day,
  SUM(APPROX_COUNT_DISTINCT(user_id)) OVER (ORDER BY day ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) ninty_day_window_apprx
FROM
  daily
GROUP BY
  1
ORDER BY
  1 DESC

Однако это привело к получению различного числа пользователей в день, а затем суммированию их - но различия могут дублироваться в окне, если они появляются несколько раз. Так что это не совсем точная оценка отдельных пользователей за 90 дней.

Следующее, что я попробовал, - это использовать следующее решение /questions/16238029/podschet-unikalnyih-identifikatorov-v-skolzyaschij-period-vremeni/16238033#16238033- объединение всех отдельных user_ids для каждого окна в массив и подсчет различий в этом.

WITH daily AS (
  SELECT date(activity_date) day, STRING_AGG(DISTINCT user_id) users
  FROM `fake-table`  
  GROUP BY day
), temp2 AS (
  SELECT
    day, 
    STRING_AGG(users) OVER(ORDER BY UNIX_DATE(day) RANGE BETWEEN 89 PRECEDING AND CURRENT ROW) users
  FROM daily
)

SELECT day, 
  (SELECT APPROX_COUNT_DISTINCT(id) FROM UNNEST(SPLIT(users)) AS id) Unique90Days
FROM temp2

order by 1 desc

Однако это быстро исчерпало память с чем-либо большим.

Затем нужно было использовать эскиз HLL для представления различных идентификаторов в гораздо меньшем значении, поэтому проблема с памятью будет меньше. Я думал, что мои проблемы были решены, но я получаю сообщение об ошибке при выполнении следующего: Ошибка просто "Функция MERGE_PARTIAL не поддерживается". Я тоже попробовал с MERGE и получил ту же ошибку. Это происходит только при использовании оконной функции. Создание эскизов для ценности каждого дня прекрасно работает.

Я прочитал документацию BigQuery Standard SQL и не вижу ничего о HLL_COUNT.MERGE_PARTIAL и HLL_COUNT.MERGE с оконными функциями. Предположительно, это должно взять 90 эскизов и объединить их в один эскиз HLL, представляющий различные значения между 90 оригинальными эскизами?

WITH
  daily AS (
  SELECT
    DATE(activity_date) day,
    HLL_COUNT.INIT(user_id) sketch
  FROM
    `fake-table`
  GROUP BY
    1
  ORDER BY
    1 DESC),

  rolling AS (
  SELECT
    day,
    HLL_COUNT.MERGE_PARTIAL(sketch) OVER (ORDER BY UNIX_DATE(day) RANGE BETWEEN 89 PRECEDING AND CURRENT ROW) rolling_sketch
    FROM daily)

SELECT
  day,
  HLL_COUNT.EXTRACT(rolling_sketch)
FROM
  rolling
ORDER BY
  1 

"Изображение ошибки - функция MERGE_PARTIAL не поддерживается"

Любые идеи, почему эта ошибка происходит или как исправить?

Answer 1

Ниже для BigQuery Standard SQL и делает именно то, что вы хотите с использованием оконной функции

#standardSQL
SELECT day,
  (SELECT HLL_COUNT.MERGE(sketch) FROM UNNEST(rolling_sketch_arr) sketch)  rolling_sketch
FROM (
  SELECT day, 
    ARRAY_AGG(ids_sketch) OVER(ORDER BY UNIX_DATE(day) RANGE BETWEEN 89 PRECEDING AND CURRENT ROW) rolling_sketch_arr 
  FROM (
    SELECT day, HLL_COUNT.INIT(id) ids_sketch
    FROM `project.dataset.table`
    GROUP BY day
  )
)

Вы можете проверить, поиграть с выше, используя [полностью] фиктивные данные, как в примере ниже

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, DATE '2019-01-01' day UNION ALL
  SELECT 2, '2019-01-01' UNION ALL
  SELECT 3, '2019-01-01' UNION ALL
  SELECT 1, '2019-01-02' UNION ALL
  SELECT 4, '2019-01-02' UNION ALL
  SELECT 2, '2019-01-03' UNION ALL
  SELECT 3, '2019-01-03' UNION ALL
  SELECT 4, '2019-01-03' UNION ALL
  SELECT 5, '2019-01-03' UNION ALL
  SELECT 1, '2019-01-04' UNION ALL
  SELECT 4, '2019-01-04' UNION ALL
  SELECT 2, '2019-01-05' UNION ALL
  SELECT 3, '2019-01-05' UNION ALL
  SELECT 5, '2019-01-05' UNION ALL
  SELECT 6, '2019-01-05' 
)
SELECT day,
  (SELECT HLL_COUNT.MERGE(sketch) FROM UNNEST(rolling_sketch_arr) sketch)  rolling_sketch
FROM (
  SELECT day, 
    ARRAY_AGG(ids_sketch) OVER(ORDER BY UNIX_DATE(day) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) rolling_sketch_arr 
  FROM (
    SELECT day, HLL_COUNT.INIT(id) ids_sketch
    FROM `project.dataset.table`
    GROUP BY day
  )
)
-- ORDER BY day

с результатом

Row day         rolling_sketch   
1   2019-01-01  3    
2   2019-01-02  4    
3   2019-01-03  5    
4   2019-01-04  5    
5   2019-01-05  6    

Answer 2

Скомбинировать HLL_COUNT.INIT а также HLL_COUNT.MERGE, Это решение использует 90-дневное перекрестное соединение с GENERATE_ARRAY(1, 90) вместо OVER,

#standardSQL
SELECT DATE_SUB(date, INTERVAL i DAY) date_grp
 , HLL_COUNT.MERGE(sketch) unique_90_day_users
 , HLL_COUNT.MERGE(DISTINCT IF(i<31,sketch,null)) unique_30_day_users
 , HLL_COUNT.MERGE(DISTINCT IF(i<8,sketch,null)) unique_7_day_users
FROM (
  SELECT DATE(creation_date) date, HLL_COUNT.INIT(owner_user_id) sketch
  FROM `bigquery-public-data.stackru.posts_questions` 
  WHERE EXTRACT(YEAR FROM creation_date)=2017
  GROUP BY 1
), UNNEST(GENERATE_ARRAY(1, 90)) i
GROUP BY 1
ORDER BY date_grp