Hive SQL 每日SQL

news/2024/11/25 19:38:11/

1、查询订单明细表(order_detail)中销量(下单件数)排名第二的商品id,如果不存在返回null,如果存在多个排名第二的商品则需要全部返回。

需要用到的表:

订单明细表:order_detail

e2d686bb24c1bd657e413ec9d700bbd0.png

代码:

select 
sku_id
from 
(
select 
sku_id
,sale_num
,dense_rank() over (order by sale_num desc ) as drp 
from 
(
select 
sku_id
,sum(sku_num) as sale_num 
from  order_detail
group by sku_id
)a
)b 
where drp = 2

结果:

2、查询订单信息表(order_info)中最少连续3天下单的用户id,期望结果如下

 

订单信息表:order_info

order_id
(订单id)

user_id
(用户id)

create_date
(下单日期)

total_amount
(订单金额)

1

101

2021-09-30

29000.00

10

103

2020-10-02

28000.00

代码

select 
distinct user_id
from 
(
select 
user_id
,date1
,case when (datediff(date2,date1)=1 and datediff(date3,date2)=1 and datediff(date3,date1)=2) then 1 else 0 end  diff
from 
(
select 
distinct user_id
,create_date as date1
,lead(create_date) over (partition by user_id order by create_date) as date2
,lead(create_date,2) over (partition by user_id order by create_date) as date3
from 
(select distinct user_id,create_date from order_info
)a
)b
)c where diff =1

结果

3、从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品,

期望结果如下:

category_id
<string>

category_name
<string>

sku_id
<string>

name
<string>

order_num
<bigint>

sku_cnt
<bigint>

1

数码

2

手机壳

302

4

2

厨卫

8

微波炉

253

4

3

户外

12

遮阳伞

349

4

需要用到的表

订单明细表:order_detail

order_detail_id
(订单明细id)

order_id
(订单id)

sku_id
(商品id)

create_date
(下单日期)

price
(商品单价)

sku_num
(商品件数)

1

1

1

2021-09-30

2000.00

2

2

1

3

2021-09-30

5000.00

5

22

10

4

2020-10-02

6000.00

1

23

10

5

2020-10-02

500.00

24

24

10

6

2020-10-02

2000.00

5

商品信息表:sku_info

sku_id
(商品id)

name
(商品名称)

category_id
(分类id)

from_date
(上架日期)

price
(商品价格)

1

xiaomi 10

1

2020-01-01

2000

6

洗碗机

2

2020-02-01

2000

9

自行车

3

2020-01-01

1000

商品分类信息表:category_info

category_id
(分类id)

category_name
(分类名称)

1

数码

2

厨卫

3

户外

代码:

with t1 as (
select 
a.category_id
,b.category_name
,count(sku_id) as sku_cnt
from sku_info a 
left join category_info b on a.category_id =b.category_id
group by 
a.category_id
,b.category_name)
,
t2 as (
select * 
from 
(
select  
category_id
,sku_id
,name
,order_num
,rank() over(partition by category_id order by order_num desc) rk
from (
select 
b.category_id 
,a.sku_id
,b.name
,sum(a.sku_num) as order_num
from order_detail  a 
left join sku_info b on a.sku_id=b.sku_id
group by 
b.category_id 
,a.sku_id
,b.name
)a
)b
where rk='1'
)select  
t2.category_id
,t1.category_name
,t2.sku_id
,t2.name
,t2.order_num
,t1.sku_cnt
from t2 
left join t1 on t2.category_id = t1.category_id

结果:

4、从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额,以及每个用户在其每个下单日期的VIP等级。

用户vip等级根据累积消费金额计算,计算规则如下:
设累积消费总额为X,
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员

期望结果如下:

user_id
<string>
(用户id)

create_date
<string>
(下单日期)

sum_so_far
<decimal(16,2)>
(截至每个下单日期的累计下单金额)

vip_level
<string>
(每个下单日期的VIP等级)

101

2021-09-27

29000.00

青铜会员

101

2021-09-28

99500.00

白金会员

101

2021-09-29

142800.00

钻石会员

101

2021-09-30

143660.00

钻石会员

102

2021-10-01

171680.00

钻石会员

102

2021-10-02

177850.00

钻石会员

103

2021-10-02

69980.00

黄金会员

103

2021-10-03

75890.00

黄金会员

104

2021-10-03

89880.00

白金会员

105

2021-10-04

120100.00

钻石会员

106

2021-10-04

9390.00

普通会员

106

2021-10-05

119150.00

钻石会员

107

2021-10-05

69850.00

黄金会员

107

2021-10-06

124150.00

钻石会员

108

2021-10-06

101070.00

钻石会员

108

2021-10-07

155770.00

钻石会员

109

2020-10-08

24020.00

青铜会员

109

2021-10-07

153500.00

钻石会员

1010

2020-10-08

51950.00

黄金会员

需要用到的表:

订单信息表:order_info

order_id
(订单id)

user_id
(用户id)

create_date
(下单日期)

total_amount
(订单金额)

1

101

2021-09-30

29000.00

10

103

2020-10-02

28000.00

代码

select  
*
,case when (sum_so_far >=0 and sum_so_far <10000) then '普通会员'when (sum_so_far >=10000 and sum_so_far <30000) then '青铜会员'when (sum_so_far >=30000 and sum_so_far <50000) then '白银会员'when (sum_so_far >=50000 and sum_so_far <80000) then '黄金会员'when (sum_so_far >=80000 and sum_so_far <100000) then '白金会员'else '钻石会员' end vip_level
from (
select   
user_id
,create_date
,sum(sum_so_far) over(partition by user_id order by create_date rows BETWEEN unbounded preceding and current row  ) as sum_so_far
from  
(
select  
user_id
,create_date
,sum(total_amount) as sum_so_far 
from order_info 
group by 
user_id
,create_date
)a
)b

5、从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例,结果保留一位小数,使用百分数显示

期望结果如下:

percentage
<string>

70.0%

需要用到的表:

订单信息表:order_info

order_id (订单id)

user_id (用户id)

create_date (下单日期)

total_amount (订单金额)

1

101

2021-09-30

29000.00

10

103

2020-10-02

28000.00

代码

with t as (
select
user_id
,create_date as date1
,lag(create_date,1,'null') over(partition by user_id order by create_date ) as date2
,lead(create_date) over(partition by user_id order by create_date ) as date3
from (select distinct user_id,create_date from order_info)a
)select 
concat(round(avg(if(datediff(date3,date1)=1,1,0))*100,1),'%') as percentage 
from t 
where date2='null'

http://www.ppmy.cn/news/9227.html

相关文章

P10 PyTorch contour meshgrid

前言&#xff1a; 这两个函数是在画梯度图中经常用到的&#xff0c;简单了解一下 参考&#xff1a; python作图之plt.contour详解_你大佬来啦的博客-CSDN博客_plt.contour 目录&#xff1a; meshgrid contour 二维 contour 三维一 meshgrid 作用 创建网格点矩阵 输入&#…

[数据结构基础]栈和队列的结构及接口函数

一. 栈 1.1 栈的概念及结构 栈是一种特殊的线性表&#xff0c;其只允许在固定的一段进行插入和删除元素的操作。进行数据插入和删除的一端成为栈顶&#xff0c;另一端称为栈底。栈结构中的数据遵循先进后出原则&#xff08;LIFO&#xff1a;Last in First Out&#xff09;&am…

【数据篇】33 # 可视化数据处理的一般方法是什么?

说明 【跟月影学可视化】学习笔记。 数据可视化的一般过程 先看有什么样的数据&#xff1a;分析真实数据然后看想从数据中了解什么信息&#xff1a;获取想要的信息再决定使用何种可视化方式呈现&#xff1a;为数据选择正确的呈现形式最后看展示的效果怎么样&#xff0c;是否…

mysql 存储过程批量删除重复数据

mysql 存储过程批量删除重复数据 表结构&#xff1a; LOAD DATA INFILE /usr/local/phone_imsi_12 replace INTO TABLE tbl_imsi2number_new FIELDS TERMINATED BY \t ENCLOSED BY (number,imsi); 先用SQL语句来进行去重操作&#xff1a; delete from tbl_imsi2number_new …

191:vue+openlayers 选择feature,固定按钮删除selected feature

第191个 点击查看专栏目录 本示例的目的是介绍如何在vue+openlayer中使用select来选择feature元素,通过按键来删除selected的feature。 直接复制下面的 vue+openlayers源代码,操作2分钟即可运行实现效果 文章目录 示例效果配置方式示例源代码(共95行)相关API参考专栏目标…

Qt编译CTK

文章目录一、CTK简介二、CTK下载三、CTK编译一、CTK简介 CTK是什么 CTK 为支持生物医学图像计算的公共开发包&#xff0c;其全称为 Common Toolkit CTK 提供了什么 当前&#xff0c;CTK 工作的主要范围包括&#xff1a; DICOM&#xff1a;提供了从 PACS 和本地数据库中查询和…

redis配置文件

redis主要配置项&#xff1a; bind 0.0.0.0 #监听地址&#xff0c;可以用空格隔开后多个监听IP protected-mode yes #redis3.2 之后加入的新特性&#xff0c;在没有设置bind IP和密码的时候,redis只允许访问 127.0.0.1:6379&#xff0c;远程访问将提示警告信息并拒绝远程访问…

java基础 多线程

线程(thread)是一个程序内部的一条执行路径。 多线程的实现方案一&#xff1a;继承Thread类 public Thread(String name) 可以为当前线程指定名称 public Thread(Runnable target) 封装Runnable任务对象成为线程对象 public Thread(Runnable target &#x…