mysql - 如何使用 Google BigQuery 根据用户 ID 和 ID 访问过的级别数查找中位数？

Question

我有大约 100,000 个用户 ID，他们正在访问 n 个级别。我需要找到每个用户访问的用户和级别的总体中位数。

我尝试根据每个级别的 ID 数量和启动应用程序的 ID 总数来使用 AVG。这些值变化很大。

查找启动该应用程序的 ID。

SELECT 
event_names, COUNT(DISTINCT id) uniques, COUNT(id) AS total 
FROM xyz.analytics_111.xyz 
WHERE (date BETWEEN "20191018" AND "20191024") AND version = "3.1" AND event_names in ("app_open","internet") AND platform = "ANDROID" 
AND id IN ( SELECT DISTINCT id FROM abc.analytics_111.abc WHERE event_names = "internet" AND internet_status = 1 ) 
GROUP BY event_names

查找每个级别的总用户数。

SELECT event_names, story_name, level, COUNT(DISTINCT id) uniques, COUNT(id) AS total 
FROM xyz.analytics_111.xyz WHERE (date BETWEEN "20191018" AND "20191024") AND version = "3.1" AND event_names in ("start_level","end_level") AND platform = "ANDROID" AND id IN ( SELECT DISTINCT id FROM abc.analytics_111.abc 
WHERE event_names = "internet" AND internet_status = 1 ) 
GROUP BY event_names, story_name, level ORDER BY event_names DESC, story_name, level

此后，我将所有级别上的用户 ID 计数总和除以启动应用程序的用户 ID 数，以获得每个用户访问的级别的平均数。有没有办法找到中位数？

Answer 1

该问题没有足够的详细信息来提供完整的答案，但您提供给我们的要素如下:

• 当您需要MEDIAN时，不要使用AVG

要计算中位数，您可以执行以下操作:

SELECT level_id, fhoffa.x.median(ARRAY_AGG(some_number))
FROM `table`
GROUP BY level_id

bqutil.fn.median() 是我们与世界共享的公共(public) UDF:

• https://medium.com/@hoffa/new-in-bigquery-persistent-udfs-c9ea4100fd83

<小时/>

现在，如果您愿意，请提供您提供的额外详细信息

to get the AVG of Levels visited by each user

，然后:

SELECT AVG(levels) avg_levels_for_users
FROM (
  SELECT id user, COUNT(DISTINCT level) levels
  FROM `....`
  GROUP BY user
)