#使用了if(condition, x, y)函数
select customer_id,
if(latest_place_order_date,1,0) as if_placed_order
from customers_info
VQ23-请按城市对客户进行排序,如果城市为空,则按国家排序
学到了使用
case
when column condition then 结果
when ……
end
select * from customers_info order by
#order by根据case when then语句来判定
(case
when city is not null then city
when city is null then country
end)
#由于select后的语句是最后运行的,所以可以在分组之后,使用case when then 语句对输出做一个过滤
select
case
when age < 20 then '20以下'
when age between 20 and 50 then '20-50'
when age > 50 then '50以上'
else '未填写'
end as age_group,count(customer_id) as user_count
from customers_info
group by age_group
使用到的函数如下:
row_number() over(condition)函数:窗口函数,能够对数据中的序号进行顺序显示
row_number的别名按组进行排序。如有三组,即1 2 3 — 1 2 — 1 2 3 4 5这种
partition by column,按照column分组,并可以返回多行结果(与group by的区别)
select date,user_id,pass_count
from (
select
date,user_id,pass_count,
#row_number() over(condition)函数:row_number函数能够对数据中的序号进行顺序显示
#partition by column,按照column分组,并可以返回多行结果(与group by的区别)
#根据日期进行分组,并且按照通过量来排序。
row_number() over(partition by date order by pass_count desc) as row_num
from questions_pass_record
)as t
where row_num <= 2