笔记1

来源：互联网发布：产品外观设计软件编辑：程序博客网时间：2024/06/16 13:32

1）求门店每月在线天数

select substring(hp_stat_time, 1,7) as month ,shopid, sum(case when charge >0 then 1 else 0 end) as online_day_cnt

,sum(charge)

from (

) a where buname="test" group by substring(hp_stat_time, 1,7),shopid

 select date_format(now(),'%Y-%c-%d %h:%i:%s');

awk '{sum += $2};END {print sum}' znz_ads

4）insert into kirin_invite_firstclass(mobile, invite, time) select invitedby as mobile, mobile as invite, FROM_UNIXTIME(createtime, '%Y/%m/%d %H:%i:%s' ) as time from kirin_member where invitedby != "";

5) SELECT a.shopid, sum(coalesce(Charge,0))Charge //coalesce表示遇到非法值返回0

6) get_json_object(targetinfo, '$.position') position

7) sum(case when start_time between "2016-06-10" and "2016-06-03" then 1 else 0 end) lastweek_user_pass_call_num统计计数哦

8)最后一天-第一天=sum(2:n+1) - sum(1:n)

9) 中间是否有上调或者下调：计算平均值相见，如果是负的表示下调，正的表示上调。

10) count(distinct case when c.inner_is_online>0 then concat(c.shopid,hp_stat_date) end)inner_lastmonthonlinedays, concat之后再distinct

11) select accountid, first_paytime from table

distribute by accountid --按照distribute分reduce

sort by accountid, first_paytime --按照first_paytime排序

0 0