随着时间的推移,这可能变得更加容易,但你不再需要手动定义每个条目。如果你可以编辑数据源,你可以在Data Studio中添加一个计算字段,公式如下:
FLOOR(age/5) * 5
如果将该字段命名为“age_bin”,则所有条目都将具有5的倍数值,带有标签(即age_bin
的值),表示桶的最小值。例如:
0
中,因为FLOOR(4/5)
=035
中,因为36/5
=7.2,所以FLOOR(7.2)
=7,且7 * 5
= 35然后,您可以使用“age_bin”作为维度,使用“Record Count”作为指标制作条形图。
CASE
WHEN Age > 0 AND Age <= 5 THEN "bin1"
WHEN Age > 5 AND Age <= 10 THEN "bin2"
WHEN Age > 10 AND Age <= 15 THEN "bin3"
WHEN Age > 15 AND Age <= 20 THEN "bin4"
ELSE "bin5"
END
之后,您可以使用任何变量(例如这里的 年龄
)作为度量,并将新变量作为维度来对数据进行分组并选择计数作为表示形式,从而创建一个柱状图。
最简单的方法是将您的数据格式化为“bin”。您可以这样做:
count(CASE WHEN Age > 0 AND Age <= 5 Then 1) AS bin1
count(CASE WHEN Age > 5 AND Age <= 10 Then 1) AS bin2
count(CASE WHEN Age > 10 AND Age <= 15 Then 1) AS bin3
count(CASE WHEN Age > 15 AND Age <= 20 Then 1) AS bin4
你可以创建一个switch case字段,我们称之为histogram_group
WHEN Days_Since_First_Touch > 1 AND Days_Since_First_Touch <= 5 THEN "(1_5)"
WHEN Days_Since_First_Touch > 5 AND Days_Since_First_Touch <= 10 THEN (5_10)"
WHEN Days_Since_First_Touch > 10 AND Days_Since_First_Touch <= 20 THEN (10_20)"
WHEN Days_Since_First_Touch > 1 AND Days_Since_First_Touch <= 5 THEN 0.0001;
WHEN Days_Since_First_Touch > 5 AND Days_Since_First_Touch <= 10 THEN 0.0002;
WHEN Days_Since_First_Touch > 10 AND Days_Since_First_Touch <= 20 THEN 0.0003;
当你在 Firebase、BigQuery 和 Data Studio 之间建立链接时,可以在 Data Studio 上编写以下查询:
CASE WHEN Days_Since_First_Touch <= 1 THEN "(0_1)"
WHEN Days_Since_First_Touch > 1 AND Days_Since_First_Touch <= 2 THEN "(1_2)"
WHEN Days_Since_First_Touch > 2 AND Days_Since_First_Touch <= 3 THEN "(2_3)"
WHEN Days_Since_First_Touch > 3 AND Days_Since_First_Touch <= 4 THEN "(3_4)"
WHEN Days_Since_First_Touch > 4 AND Days_Since_First_Touch <= 5 THEN "(4_5)"
WHEN Days_Since_First_Touch > 5 AND Days_Since_First_Touch <= 6 THEN "(5_6)"
WHEN Days_Since_First_Touch > 6 AND Days_Since_First_Touch <= 7 THEN "(6_7)"
WHEN Days_Since_First_Touch > 7 AND Days_Since_First_Touch <= 8 THEN "(7_8)"
WHEN Days_Since_First_Touch > 8 AND Days_Since_First_Touch <= 9 THEN "(8_9)"
WHEN Days_Since_First_Touch > 9 AND Days_Since_First_Touch <= 10 THEN "(9_10)"
WHEN Days_Since_First_Touch > 10 AND Days_Since_First_Touch <= 15 THEN "(10_15)"
WHEN Days_Since_First_Touch > 15 AND Days_Since_First_Touch <= 20 THEN "(15_20)"
WHEN Days_Since_First_Touch > 20 AND Days_Since_First_Touch <= 30 THEN " (20_30)"
WHEN Days Since First Touch > 30 THEN "N (30+)"
ELSE NULL END
对于动态的箱子大小,您可以在SQL:2011(PostgreSQL、bigquery、MariaDB、SQL Server 2016等)中执行以下操作:
首先,您需要声明箱子的大小(以年为单位):
WITH params (binsize) AS (
SELECT 5
),
SELECT
age,
CONCAT(FLOOR(age / (SELECT binsize FROM params)) * (SELECT binsize FROM params), '-', FLOOR(age / (SELECT binsize FROM params)) * (SELECT binsize FROM params) + (SELECT binsize FROM params)) AS age_bin
var1,
var2,
...
FROM ...