实现开窗count distinct 历史累计截止日期去重统计

已于 2022-02-24 18:39:49 修改
阅读量7.5k 收藏 27
点赞数 4
分类专栏： hive-sql语法 文章标签： hive hadoop 数据仓库 sql etl
于 2022-02-24 18:32:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuya19921123/article/details/123118027
版权
在Hive中，当面临需要计算历史累计去重计数的场景，例如会员历史累计消费人数，传统的`count(distinct)`开窗函数无法直接实现。本文介绍了两种解决方案：一是通过`sum`函数结合排序来统计每日新增并累计；二是利用`collect_set`配合`size()`函数。这两种方法可以有效应对实际统计需求，并详细解析了Hive的窗口函数`OVER(PARTITION BY)`的使用。
摘要由CSDN通过智能技术生成

解决问题：hive中count(distinct ) over() 无法使用场景

累计去除统计，实际经常使用到的场景比如会员每日历史累计消费，项目每日累计营收等。

案例：

数据准备：

用户轨迹用户访问日志表 test_visit_tab

cookieid(用户id) uvdate(访问时间) pagename(浏览页面) pv(访问次数)
cookie1 2022-02-01  A_page  1
cookie1 2022-02-01  B_page  2
cookie1 2022-02-02  A_page  1
cookie1 2022-02-02  B_page  3
cookie2 2022-02-01  A_page  1
cookie2 2022-02-01  B_page  5
cookie2 2022-02-03  A_page  2
cookie3 2022-02-02  A_page  6
cookie3 2022-02-02  B_page  3
cookie3 2022-02-03  A_page  1
cookie3 2022-02-03  B_page  2
cookie3 2022-02-04  A_page  1
cookie3 2022-02-04  B_page  5
cookie4 2022-02-05  A_page  1
cookie4 2022-02-05  B_page  4

需要统计的效果按照uvdate时间统计截止当前访问人数，统计效果

uvdate uv(当天uv) add_uv(截止当天uv)
2022-02-01  2   2
2022-02-02  2   3
2022-02-03  2   3
2022-02-04  1   3
2022-02-05  1   4

开窗count(distinct )over(order by)尝试


                

        

        

    




        

            

                    
                      最低0.47元/天 解锁文章
                        
                    
            

        

    

      

        

            

              
                
                  村口蹲点的阿三
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    4
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    27
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
spark count(distinct)over() 数据处理

				
			

			

				

					dbc_zt的博客
				

				

					12-02
					
					2628
					
				

			

		

		

			
				
spark count(distinct)over() 数据处理
业务描述

有这么一个业务，需要过滤排除掉相同设备不同账号，以及相同账号不同设备的数据，只留下设备与账号一对一的数据：
如果从关系型数据库来看，A 多对对 B, 需要找出并保留 A一对一的B。

数据准备




设备id
账号




1
a


2
b


2
c


3
b


3
d


4
d


5
e


5
e


/*
从数据上看只有(1,a),(5,e)满足一对一的要求，
设备2存在b/c两个账号，设备3存在b/d两个账号

			
		

	



                

              
                



	

		

			

				
					
SQL开窗函数

				
			

			

				

					HelloWorld
				

				

					06-28
					
					540
					
				

			

		

		

			
				
SQL开窗函数
八股文式开窗学习笔记，关于开窗函数是什么，有什么用，我想百度百科说的一定比我们明白，但他究竟好在哪里，举一个简单的leetcode例子

如果按照传统基础sql语法，那么他的答案为：
select s1.Score,count(distinct(s2.score)) `Rank`
from
Scores s1,Scores s2
where
s1.score<=s2.score
group by s1.Id
order by `Rank`


解释一下这7行代码,将一张表当作两张使用分

			
		

	



                


  
              

                


	

		

			

				
					
sql中开窗函数简单说明

				
			

			

				

					Mr.cheng的博客
				

				

					04-25
					
					1万+
					
				

			

		

		

			
				
开窗函数与聚合函数一样，也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。 
 语法:主要是over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. ) 
常用函数：（最常用的应该是1.2.3 的排序） 
1、row_number() over(par

			
		

	





	

		

			

				
					
如何在Excel快速筛选出同一人在同一天内的相同数据记录？

					
最新发布

				
			

			

				

					littlespider889的博客
				

				

					07-01
					
					1197
					
				

			

		

		

			
				
如何在Excel快速筛选出同一人在同一天内的相同数据记录？

			
		

	



		

			
		



	

		

			

				
					
【SQL】Sql如何用开窗函数实现去重

				
			

			

				

					tting_的博客
				

				

					05-24
					
					2548
					
				

			

		

		

			
				
直接上代码啦，注释很详细啦


--先drop再create实现重跑机制
drop table if exists table_a;
create table  table_a
row format delimited fields terminated
by '\001' collection items terminated 
by '\002' map keys terminated
by '\003' lines terminated
by '\n' stored as textfile as 



			
		

	





	

		

			

				
					
使用窗口函数组内去重合计

				
			

			

				

					qq_41115476的博客
				

				

					08-11
					
					939
					
				

			

		

		

			
				
使用窗口函数组内去重合计

			
		

	





	

		

			

				
					
HIVE中窗口函数

				
			

			

				

					Miracle.Zhao的博客
				

				

					04-10
					
					3980
					
				

			

		

		

			
				
什么是窗口函数

窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。 大家都知道聚合函数是将某列中多行的值合并为一行，比如sum、count等。 而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。 通用的窗口函数可以用下面的语法来概括：


Function() Over (Partition By Column1，Column2，Order By Column3)

窗口函数又分为以下三类： 聚合型窗口函数 分析型窗口函数 * 取值型窗口函数

接下来我们将通过几个实际

			
		

	





	

		

			

				
					
开窗函数ROW_NUMBER() OVER()函数用法详解，并巧妙使用ROW_NUMBER()函数进行数据去重

					
热门推荐

				
			

			

				

					weixin_46574678的博客
				

				

					01-18
					
					1万+
					
				

			

		

		

			
				
ROW_NUMBER() OVER()函数用法详解，并巧用ROW_NUMBER()函数删除重复数据

			
		

	





	

		

			

				
					
spark实现用窗口函数进行去重计数的功能

				
			

			

				

					longwei92的博客
				

				

					06-27
					
					2385
					
				

			

		

		

			
				
df.withColumn("new_col_name", size(collect_set($"need_count_col_name").over(Window.partitionBy($"window_col_name"))))
//同理在hive中也可以采用这种利用size和collect_set的形式实现用窗口函数进行去重计数的功能


...

			
		

	





	

		

			

				
					
HIVE、Oracle种row_number开窗函数去重、分组排序

				
			

			

				

					MrGrant.blog
				

				

					12-11
					
					2400
					
				

			

		

		

			
				
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wiborgite/article/details/80521593
ROW_NUMBER() OVER函数的基本用法
语法：ROW_NUMBER() OVER(PARTITION BY COLUMNORDER BY COLUMN)
详解：...

			
		

	





	

		

			

				
					
Mysql中distinct与group by的去重方面的区别

				
			

			

				

					12-14
				

			

		

		

			
				
总结起来，`DISTINCT` 是一种简单的去重工具，适用于不需要额外统计信息的场景，而 `GROUP BY` 则是数据聚合和统计的核心工具，用于提供更深入的数据洞察。理解并正确运用这两个关键字，对于优化SQL查询和提升数据库...

			
		

	





	

		

			

				
					
C# 实现Distinct将对象按条件去重

				
			

			

				

					01-19
				

			

		

		

			
				
平时，我们将c#中的Distinct大多用于对数组去重，一般数组为基础的数据类型，例如 int,string.也可以用于对象去重，我们看看C#对Distinct方法的定义：  有重载，第一个参数都加了this,是拓展方法，有关拓展方法，请...

			
		

	





	

		

			

				
					
Mongodb聚合函数count、distinct、group如何实现数据聚合操作

				
			

			

				

					09-10
				

			

		

		

			
				
 两种方法的主要区别在于，`distinct` 方法仅返回去重后的值集合，而 `runCommand` 还会提供统计详情。  3. **group**：  `group` 函数是最复杂的聚合函数之一，它可以用于更复杂的统计需求。通过指定键（key）、...

			
		

	





	

		

			

				
					
MySQL去重该使用distinct还是group by？

				
			

			

				

					09-08
				

			

		

		

			
				
在进行数据库查询时，经常会遇到需要去重的情况，MySQL提供了两种方法来实现去重，一种是使用DISTINCT关键字，另一种是使用GROUP BY语句。这两者在不同的场景下表现不同，选择合适的方法可以提高查询效率。  首先，...

			
		

	





	

		

			

				
					
MongoDB教程之聚合（count、distinct和group）

				
			

			

				

					09-10
				

			

		

		

			
				
总结来说，MongoDB 的聚合功能是其强大之处，`count`、`distinct` 和 `group` 提供了对数据进行统计、查找唯一值和复杂分组的能力，从而满足了数据分析和报告的各种需求。在实际项目中，这些聚合操作可以帮助开发者...

			
		

	





	

		

			

				
					
Hive三种去重方法，distinct,group by与ROW_Number()窗口函数

				
			

			

				

					u011250186的博客
				

				

					02-10
					
					5467
					
				

			

		

		

			
				
订阅专栏
一、distinct,group by与ROW_Number()窗口函数使用方法
1. Distinct用法：对select 后面所有字段去重，并不能只对一列去重。
（1）当distinct应用到多个字段的时候，distinct必须放在开头，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个字段，而且distinct只能放到所有字段的前面

（2）distinct对NULL是不进行过滤的，即返回的结果中是包含NULL值的

（3）聚合函数中的DISTINCT,如 COUNT( ) 会过滤掉为.

			
		

	





	

		

			

				
					
窗口函数的介绍

				
			

			

				

					weixin_45817985的博客
				

				

					10-21
					
					2391
					
				

			

		

		

			
				
窗口函数

			
		

	





	

		

			

				
					
窗口函数 row_number 去重复

				
			

			

				

					bbbeoy的专栏
				

				

					02-15
					
					3601
					
				

			

		

		

			
				
ROW_NUMBER 的使用基本上倒不会遗忘了, 这是很久以前的代码库了, 整理出来对初学者有帮助.

如果去继续研究研究 SQL Server 2012 中的Window Function 新特性, 就会发现里面还有更多很强大的东东, 很多内容和这里的 ROW_NUMBER 的概念或者使用方式有些相似.

理解了ROW_NUMBER 以及 PARTITION BY 的使用再去看 2012

			
		

	





	

		

			

				
					
Hive--count(distinct)优化

				
			

			

				

					qq_46893497的博客
				

				

					09-30
					
					3486
					
				

			

		

		

			
				
前言

使用count distinct计算两列联合去重时，若有任何一列为NULL，那么count计数时就会略过这一条。

如：count(distinct a,b) 若a或者b中有一个是null，那么这一行就不会参与计数
平台：hive、mysql都是如此







id
tools
name



1	hive	a
2	hive	b
3	hive	NULL
4	NULL	m
6 观察以下代码执行结果

...

			
		

	





	

		

			

				
					
hive 开窗去重统计

				
			

			

				

					07-28
				

			

		

		

			
				
您好！针对Hive开窗去重统计，可以使用Hive的窗口函数和Distinct关键字来实现。以下是一个示例查询，供参考：

```sql
SELECT 
    column1,
    column2,
    COUNT(DISTINCT column3) AS distinct_count
FROM
    your_table
WINDOW w AS (
    PARTITION BY column1, column2
    ORDER BY column3
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
GROUP BY
    column1,
    column2
```

在上述查询中，您可以将 "your_table" 替换为您的表名，"column1"、"column2"、"column3" 替换为您的实际列名。通过窗口函数和DISTINCT关键字，可以在窗口范围内对column3进行去重统计，然后按照column1和column2进行分组。

希望对您有所帮助！如果还有其他问题，请随时提问。

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值