时间戳汇总 [英] Aggregation by timestamp
问题描述
SEO> SEO>付费1
付费>付费>会员>付费1
SEO>会员1我有一个查询,该查询的结果包含客户ID号,营销渠道,时间戳和购买日期。因此,结果可能看起来像这样。
SEO > SEO > Paid 1 Paid > Paid > Affiliate > Paid 1 SEO > Affiliate 1I have a query that results in a data containing customer id numbers, marketing channel, timestamp, and purchase date. So, the results might look something like this.
id marketingChannel TimeStamp Transaction_date
1 SEO 5/18 23:11:43 5/18
1 SEO 5/18 24:12:43 5/18
1 Paid 5/18 24:13:43 5/18
2 Paid 5/18 24:12:43 5/18
2 Paid 5/18 24:14:43 5/18
2 Affiliate 5/18 24:20:43 5/18
2 Paid 5/18 24:22:43 5/18
3 SEO 5/18 24:10:43 5/18
3 Affiliate 5/18 24:11:43 5/18
我想知道是否存在查询来以显示营销路径计数的方式聚合此信息。
I'm wondering if there is a query to aggregate this information in a fashion that show the count of marketing paths.
例如。
Marketing Path Count
SEO > SEO > Paid 1
Paid > Paid > Affiliate > Paid 1
SEO > Affiliate 1
我正在考虑编写Python脚本来获取此信息,但我想知道是否有是SQL中的简单解决方案-因为我不熟悉SQL。
I'm thinking about writing a Python script to get this information, but am wondering if there is a simple solution in SQL - as I'm not as framiliar with SQL.
推荐答案
几年前,我需要类似的结果我测试了在Teradata中获取串联字符串的不同方法。顺便说一句,如果行数太多并且连接的字符串超过64000个字符,所有操作都可能失败。
Some years ago I needed a similar result and I tested different ways to get a concatenated string in Teradata. Btw, all might fail if the number of rows is too high and the concatenated string exceeds 64000 chars.
最有效的是用户定义函数(用C编写):
The most efficient was a User Defined Function (written in C):
SELECT
PATH
,COUNT(*)
FROM
(
SELECT
DelimitedBuildSorted(MARKETINGCHANNEL
,CAST(CAST(ts AS FORMAT 'yyyymmddhhmiss') AS VARCHAR(14))
,'>') AS PATH
FROM t
GROUP BY id
) AS dt
GROUP BY 1;
如果您需要经常和/或在大型表上运行该查询,则可以与您的DBA联系如果可以使用UDF(大多数DBA不喜欢它们,因为它们是用他们不知道的语言编写的,C)。
If you need to run that query frequently and/or on a large table you might talk to your DBA if a UDF is possible (most DBAs don't like them as they're written in a language they don't know, C).
如果每个id的平均行数很低。
Joseph B的版本可以简化一些,但是最重要的是创建一个临时表,而不是使用View或Derived Table进行ROW_NUMBER计算。这样会导致更好的计划(在SQL Server中也是如此):
Recursion might be ok if the average number of rows per id is low. Joseph B's version can be a bit simplified, but the most important thing is to create a temporary table instead of using a View or Derived Table for the ROW_NUMBER calculation. This results in a better plan (in SQL Server, too):
CREATE VOLATILE TABLE vt AS
(
SELECT
id
,MarketingChannel
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY TS DESC) AS rn
,COUNT(*) OVER (PARTITION BY id) AS max_rn
FROM t
) WITH DATA
PRIMARY INDEX (id)
ON COMMIT PRESERVE ROWS;
WITH RECURSIVE cte(id, path, rn) AS
(
SELECT
id,
-- modify VARCHAR size to fit your maximum number of rows, that's better than VARCHAR(64000)
CAST(MarketingChannel AS VARCHAR(10000)) AS PATH,
rn
FROM vt
WHERE rn = max_rn
UNION ALL
SELECT
cte.ID,
cte.PATH || '>' || vt.MarketingChannel,
cte.rn-1
FROM vt JOIN cte
ON vt.id = cte.id
AND vt.rn = cte.rn - 1
)
SELECT
PATH,
COUNT(*)
FROM cte
WHERE rn = 1
GROUP BY path
ORDER BY PATH
;
您也可以尝试旧学校MAX(CASE):
You might also try old school MAX(CASE):
SELECT
PATH
,COUNT(*)
FROM
(
SELECT
id
,MAX(CASE WHEN rnk = 0 THEN MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 1 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 2 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 3 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 4 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 5 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 6 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 7 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 8 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 9 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 10 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 11 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 12 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 13 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 14 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 15 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 16 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 17 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 18 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 19 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 20 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 21 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 22 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 23 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 24 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 25 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 26 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 27 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 28 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 29 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 30 THEN '>' || MarketingChannel ELSE '' END) ||
MAX(CASE WHEN rnk = 31 THEN '>' || MarketingChannel ELSE '' END) AS PATH
FROM
(
SELECT
id
,TRIM(MarketingChannel) AS MarketingChannel
,RANK() OVER (PARTITION BY id
ORDER BY TS) -1 AS rnk
FROM t
) dt
GROUP BY 1
) AS dt
GROUP BY 1;
我最多可以合并2048行,每行30个字符:-)
I had up to concat 2048 rows with 30 chars each :-)
SELECT
PATH
,COUNT(*)
FROM
(
SELECT
id
,MAX(CASE WHEN rnk MOD 16 = 0 THEN path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 1 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 2 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 3 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 4 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 5 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 6 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 7 THEN '>' || path ELSE '' END) AS PATH
FROM
(
SELECT
id
,rnk / 16 AS rnk
,MAX(CASE WHEN rnk MOD 16 = 0 THEN path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 1 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 2 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 3 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 4 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 5 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 6 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 7 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 8 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 9 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 10 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 11 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 12 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 13 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 14 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 15 THEN '>' || path ELSE '' END) AS path
FROM
(
SELECT
id
,rnk / 16 AS rnk
,MAX(CASE WHEN rnk MOD 16 = 0 THEN path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 1 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 2 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 3 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 4 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 5 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 6 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 7 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 8 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 9 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 10 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 11 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 12 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 13 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 14 THEN '>' || path ELSE '' END) ||
MAX(CASE WHEN rnk MOD 16 = 15 THEN '>' || path ELSE '' END) AS path
FROM
(
SELECT
id
,TRIM(MarketingChannel) AS PATH
,RANK() OVER (PARTITION BY id
ORDER BY TS) -1 AS rnk
FROM t
) dt
GROUP BY 1,2
) dt
GROUP BY 1,2
) dt
GROUP BY 1
) dt
GROUP BY 1
这篇关于时间戳汇总的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!