我们可以通过嵌套组获得每个用户的最新可用平均季度收入吗?sqlite [英] can we get the latest available avg quarterly income of each user with nested group by ? sqlite
问题描述
我们可以找到每个嵌套组的用户最新可用季度收入的平均值吗?sqlite(考虑了一段时间,如果有不清楚的请评论)更新,有一个限制:不加大小写,因为它需要我们提前知道 3 个(在我看来并不实用)
Can we find the average of latest available quarterly income of each user with nested group by ? sqlite ( been thinking about it for a while please comment if something is not clear) updated with one restriction: do without case since it requires us to know 3 ahead of time(seem to me not vary practical)
假设我们的收入表如下:
Let's say we have the income table as:
income: uid (string), jobid (string), month (string), quarter(string), year (integer), earned (integer)
A01 uber jan w 2021 2500
A01 uber dec f 2020 1500
A01 uber nov f 2020 2000
A01 uber oct f 2020 1500
A01 uber sep f 2020 2000
A01 uber aug f 2020 1500
A03 dj aug f 2020 1500
A03 dj jul f 2020 2000
A03 dj jun f 2020 1500
A03 dj may s 2020 2000
A02 chef nov f 2020 2000
A02 chef oct f 2020 1500
A02 chef sep f 2020 2000
希望使用一个查询返回(如果可能,按 uid 排序):
would like use one query to return (order by uid if possible):
A01 w 2021 2500
A02 f 2020 1833.33
A03 f 2020 1666.66
到目前为止我所拥有的:
What I have so far:
select uid, quarter,year, avg(earned)
from income
group by year, quarter;
哪个返回:
A01 w 2021 2500
A01 f 2020 1700
A03 f 2020 1666.66
A03 s 2020 2000
A02 f 2020 1833.33
注意方式
A01 f 2020 1700
A03 s 2020 2000
这里是额外的,我知道我们可以运行另一个查询来消除,但是有人能想到通过一个查询获得所需的结果吗?
is extra here, I know we can run another query to eliminate, but can someone think of getting the desired result with one query?
推荐答案
您可以使用 ROW_NUMBER
和 CASE
表达式来标识最近的年份和季度.>
You may use ROW_NUMBER
along with a CASE
expression to identify the latest year and quarter.
WITH cte AS (
SELECT uid, year, quarter, AVG(earned) AS avg_earned,
ROW_NUMBER() OVER (PARTITION BY uid
ORDER BY year DESC, CASE quarter WHEN 'f' THEN 3
WHEN 's' THEN 2
WHEN 'w' THEN 1 END DESC) rn
FROM income
GROUP BY uid, year, quarter
)
SELECT uid, year, quarter, avg_earned
FROM cte
WHERE rn = 1;
这篇关于我们可以通过嵌套组获得每个用户的最新可用平均季度收入吗?sqlite的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!