效果图网站大全,济南智能网站建设哪家好,网站域名是什么,企业oa办公软件ARPU#xff1a;人均付费总收入/总人数ARPPU#xff1a;付费用户人均付费总收入/付费人数业务要求#xff1a;1.各地市用户数、总费用(ARPU之和)是多少#xff1f;2.表一中各地市ARPU(0,30),[30,50),[50-80),[80以上)用户数分别是多少#xff1f;3.表二中用户有重复的记录…ARPU人均付费总收入/总人数ARPPU付费用户人均付费总收入/付费人数业务要求1.各地市用户数、总费用(ARPU之和)是多少2.表一中各地市ARPU(0,30),[30,50),[50-80),[80以上)用户数分别是多少3.表二中用户有重复的记录找出重复的用户1.各地市用户数、总费用(ARPU之和)是多少SELECT 城市,COUNT(DISTINCT 用户ID) as 用户数,sum(ARPU) as 总费用 from arpu值GROUP BY 城市2.表一中各地市ARPU(0,30),[30,50),[50-80),[80以上)用户数分别是多少SELECT 城市,SUM(CASE WHEN ARPU30 AND ARPU0 THEN 1 ELSE 0 END) AS (0,30),SUM(CASE WHEN ARPU30 AND ARPU50 THEN 1 ELSE 0 END) AS [30,50),SUM(CASE WHEN ARPU50 AND ARPU80 THEN 1 ELSE 0 END) AS [50,80),SUM(CASE WHEN ARPU80 THEN 1 ELSE 0 END) AS 80from arpu值GROUP BY 城市3.表二中用户有重复的记录找出重复的用户#方法一SELECT t.用户ID FROM(SELECT 用户ID,COUNT(1) AS num FROM 套餐费用GROUP BY 用户ID)twhere t.num1#方法二SELECT 用户ID FROM 套餐费用GROUP BY 用户IDHAVING count(1)1Python求解1.各地市用户数、总费用(ARPU之和)是多少dfpd.read_csv(C:/Users/andyf/Desktop/ARPU.csv)df_countdf.groupby([城市,用户ID]).count().reset_index().groupby(城市)[用户ID].count().reset_index()df_ARPUdf.groupby(城市)[ARPU].sum().reset_index()print(df_count.merge(df_ARPU,on城市))2.表一中各地市ARPU(0,30),[30,50),[50-80),[80以上)用户数分别是多少dfpd.read_csv(C:/Users/andyf/Desktop/ARPU.csv)df[label]pd.cut(df[ARPU],bins[0,30,50,80,1000],rightFalse)print(df.pivot_table(index城市,columnslabel,values用户ID,aggfunccount).fillna(0))3.表二中用户有重复的记录找出重复的用户dfpd.read_csv(C:/Users/andyf/Desktop/套餐费用.csv)print(df[df[用户ID].duplicated()][用户ID])print(df[df[用户ID].duplicated(keeplast)][用户ID])
