-- 3.3 产品贡献定量分析（帕累托分析）##累积销售额占比=累积销售额/总销售额select *from (select item_category,sum(amount) as 销售额,sum(sum(amount)) over(order by sum(amount) desc) as 累积销售额,#当over中指定了排序,但是没有指定分区和指定滑动窗口范围时,默认计算当
-- 练习（得昂）：查询每门课程的前三名：课程，第一名（姓名+分数），第二名（姓名+分数），第三名（姓名+分数）create table t_stu_profile(stu_id varchar(10) primary key,stu_name varchar(10),gender varchar(10),age int,class_id varchar(10) not null);create
-- 3.2.3 每日浏览—加购—购买的转化率select 日期, sum(if(behavior_type='pv',用户数,0)) as 浏览人数, sum(if(behavior_type='cart',用户数,0)) as 加购人数, sum(if(behavior_type='buy',用户数,0)) as 购买人数, sum(if(behavior_type=
-- 3.2.2 浏览—收藏-加购—购买的转化率selectbehavior_type,count(distinct user_id) as 用户数,lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior_type='cart',3,4))))
-- 3.2.1 浏览—加购—购买的转化率select behavior_type, count(distinct user_id) as 用户数, lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior_type='cart',
-- 3.2 行为转化分析(转化率=当前行为用户数/上一行为用户数)select behavior_type, count(distinct user_id) as 用户数, lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior
-- 3.1.3 练习:每小时PV、UV、人均浏览量、成交量、成交额select 小时, sum(behavior_type='pv') as 浏览量, count(distinct user_id) as 访客数, sum(behavior_type='pv')/count(distinct user_id) as 人均浏览量, sum(behavior_type='b
-- 3.1.2 练习:周一至周日PV、UV、人均浏览量、成交量、成交额select 星期, sum(behavior_type='pv') as 浏览量, count(distinct user_id) as 访客数, sum(behavior_type='pv')/count(distinct user_id) as 人均浏览量, sum(behavior_type=
-- 2 数据预分析select count(distinct user_id) as 用户数, count(distinct item_id) as 商品数,count(distinct item_category) as 类目数from UserBehavior_new;select behavior_type,count(*) as 行为次数from UserBehavior_n
-- 1.4 字段处理：根据times字段增加计算字段用户行为日期、周和小时，排除后续分析不需要的user_geohash字段，并将筛选后的结果保存到新表select user_id, item_id, item_category, behavior_type, date(times) as 日期, hour(times) as 小时, date_format
-- 淘宝案例数据准备create database taobao1123;use taobao1123;create table UserBehavior(user_id int, item_id int,item_category int, behavior_type varchar(10), user_geohash varchar(10), times date
问题:mysql报错1175Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Ed
多个上市公司 每一个上市公司的多年的三张财务报表如何设计这个数据机构比较好？
create database ccc;use ccc;create table order_info(c_id varchar(10),c_name varchar(10),city_id varchar(10),city_name varchar(10),order_id varchar(10),order_date date);insert into order_info values('1
-- 45 前后函数 -- 查询每个用户按单号顺序,前一笔订单距离当前订单的间隔天数 select *, lag(create_date,1) over(partition by user_no order by order_id) as 前一笔订单下单日期, timestampdiff(day,lag(create_date,1 ) over(partition by user_n
-- 44 序号函数-- 查询每个用户订单金额的排名 select *, row_number() over(partition by user_no order by amount desc ) as '订单金额排序方式1 row_number', rank() over(partition by user_no order by amount desc) as '订单金额排序方式2 ra
#开窗函数课堂练习#课堂练习1 查询每一天的累计3日订单金额总和,即计算当日及前2日金额总和select user_no,create_date,sum(amount) over (partition by user_no order by create_date range between interval 2 day preceding and interval 0 day following
#第六大部分-- 开窗函数##准备数据create table order_tab(order_id int,user_no varchar(3),amount int,create_date date);insert into order_tab values(101,'001',100,'2019-01-01'),(211,'001',300,'2019-01-02'),(308,'001',
-- 41 逻辑函数-- if函数：查询每位员工的工资级别：3000及以上为高，1500-3000为中，1500及以下为低SELECT ename,sal,if (sal >=3000,"高",if( sal>1500,"中","低")) as 工资级别FROM EMP; ## 练习 查询每位员工的实发工资(基本工资+提成,没有提成计为0)(用if函数)SELECT ename,sal,com
关注
换一批
480.0000