如何在Teradata SQL中计算每个客户端在选定月份的平均操作数

Question

我在Teradata SQL中有一个包含事务的表，如下所示：

ID   | trans_date
-------------------
123  | 2021-09-15
456  | 2021-10-20
777  | 2021-11-02
890  | 2021-02-14
...  | ...

我需要计算客户在一个月内进行的平均交易数量：09、10和11，因此我需要以下内容：

Month  | Avg_num_trx
--------------------------------------------------------
09     | *average number of transactions per client in month 09*
10     | *average number of transactions per client in month 10*
11     | *average number of transactions per client in month 11*

如何在Teradata SQL中执行taht？

Answer 1

不太熟悉Teradata，您可能会从trans_date中提取月份，然后对id和月份进行分组，并添加count(id(。在此基础上，您可以按avg(count_id(对月份进行分组。类似这样的东西-

WITH extraction AS(
SELECT 
ID,
EXTRACT (MONTH FROM trans_date) AS MM
FROM your_table)
,
WITH id_counter AS(
SELECT
ID,
MM,
COUNT(ID) as id_count
FROM extraction
GROUP BY ID, MM)
SELECT
MM,
AVG(id_count) AS Avg_num_trx
FROM id_counter
ORDER BY MM;

第一个CTE从trans_date开始抓取月份。第二个CTE将ID和带有count(ID(的月份分组，应将该客户端ID在该月份的总操作数作为ID_count。最后一个表获得按月份分组的id_count的平均值，这应该是该期间每个客户端的平均交互次数。

如果EXTRACT由于某种原因不起作用，您也可以尝试STRTOK(trans_date，'-'，2(。

其他潜在的替代方法-

--current
EXTRACT (MONTH FROM trans_date) AS MM
--option 1
STRTOK(trans_date, '-', 2) AS MM
--option 2
LEFT(RIGHT(trans_date, 5),2) AS MM

上面被重新设计为子查询-应该有助于调试-

SELECT
MM,
AVG(id_count) AS Avg_num_trx
FROM (SELECT
ID,
MM,
COUNT(ID) as id_count
FROM (SELECT 
ID,
EXTRACT (MONTH FROM trans_date) AS MM
FROM your_table) AS a
GROUP BY ID, MM) AS b
ORDER BY MM;

Answer 2

这将返回预期的答案：

SELECT 
Extract (MONTH From trans_date) AS MM,
Cast(Count(*) AS FLOAT) / Count(DISTINCT id)
FROM my_table
GROUP BY MM

与@procopypaster的答案进行比较，看看哪一个对您的数据更有效。