我们可以通过嵌套组获得每个用户的最新可用平均季度收入吗?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屋！