如何根据"gender"和" age_group"获得总"income"？收入本身不是列，它是通过查询创建的

Question

我有三个表，我建立了它们之间的关系，但无法得到我想要的。你能帮我解决下面的问题吗？

App_Purchase_001、App_Purcase_002和App_Purchase_003是采购(收入)

我已将event_name_id列添加到原始产品表中

表格为：

select * from account

有80行

女性男男男女性男

用户id	设备id
用户_ 000001		设备_001	44
用户_ 000002	Device_002	46	女性
用户_ 000003	Device_003	24
用户_ 000004	Device_004	47	女性
用户_ 000005	Device_005	29
用户_ 000006	设备_006	55
用户_ 000007	设备_007	30	女性
用户_ 000008	Device_008	22
用户_ 000009	Device_009	45

您可以将数据写入CTE，以使用类似于以下代码的东西在帐户级别聚合收入。我修改了WHERE子句以使用LIKE，而不是列出不同的购买类型，因此您可能需要将其更改为更具体的内容：

;WITH CTEIndividual
AS (
SELECT A.Gender,
COUNT(E.EventName) * P.Price AS Income,
(CASE
WHEN A.Age BETWEEN 0 AND 18 THEN '0-18'
WHEN A.Age BETWEEN 19 AND 24 THEN '19-24'
WHEN A.Age BETWEEN 25 AND 30 THEN '25-30'
WHEN A.Age BETWEEN 31 AND 40 THEN '31-40'
WHEN A.Age BETWEEN 41 AND 55 THEN '41-55'
END
) AS age_group
FROM #TempAccount AS A
INNER JOIN #TempEvent AS E ON E.UserID = A.UserID AND E.DeviceID = A.DeviceID
INNER JOIN #TempProduct AS P ON E.EventName = P.EventNameID
WHERE E.EventName LIKE 'App_Purchase%'
GROUP BY (CASE
WHEN A.Age BETWEEN 0 AND 18 THEN '0-18'
WHEN A.Age BETWEEN 19 AND 24 THEN '19-24'
WHEN A.Age BETWEEN 25 AND 30 THEN '25-30'
WHEN A.Age BETWEEN 31 AND 40 THEN '31-40'
WHEN A.Age BETWEEN 41 AND 55 THEN '41-55'
END
),
A.Gender,
P.Price
)

然后你可以用这样的东西来总结这一切：

SELECT C.Gender,
SUM(C.Income) AS Income,
C.age_group
FROM CTEIndividual AS C
GROUP BY C.Gender,
C.age_group
ORDER BY C.Gender DESC,
C.age_group;