資料産品-資料名額标簽常用sql函數

SQL能力是作為資料産品經理必不可少的技能，當然，作為資料産品，我們對SQL的查詢效率的要求可能不像開發那麼高。而對于SQL的學習一般也是隻需懂DQL查詢語言就行，對于DCL、DDL、DML這些一般隻是簡單了解就可以。當然，SQL中會有一系類功能強大的函數，個人感覺，能夠把常用的學會就能解決大部分的問題了，正所謂二八法則。對于日常業務中遇到的需要較為複雜的函數則可通過百度去查找。SQL的運用更多的是了解表之間的業務邏輯，才能夠查詢出滿足業務需求的資料。這篇文章分享一下我在項目過程中常遇到的SQL函數，以後也會不斷更新。

正常查詢，進行表關聯，常用的是left join和inner join，通過where和on進行條件限制，然後用order by和group by進行分組排序

select a.materialid,sum(a.dragtimes) as use_yx_cnt30 from
(select materialid,dragtimes,schemeid from 3D_point_kudu.materialInfo
where datediff(now(),sendtime )<=30)a
left join 
(select id,scheme_name from ugc_scheme_kudu.home_scheme)b
on a.schemeid=b.id
group by a.materialid
order by use_yx_cnt30 desc
           

使用case when…then…else…end進行字段分層,在建構資料标簽的時候常會用到，因為需要把不同範圍的資料聚成一類

--收藏等級
--字段名：save_level
select a.materialid,b.collectcount,
case when b.collectcount>=2000 then '五顆星'
     when b.collectcount>=1000 and b.collectcount<2000 then '四顆星'
     when b.collectcount>=500  and b.collectcount<1000 then '三顆星'
     when b.collectcount>=100  and b.collectcount<500  then '兩顆星'
     when b.collectcount<100 then '一顆星'
     end as save_level
from
(select materialid from pmc_kudu.designmaterial
where organid='C00000022' and is3D=1 and isdelete=0)a
inner join
(select materialid,collectcount from pmc_kudu.modelcollectext
where collectcount>0)b
on a.materialid=b.materialid
           

使用row_number() over(partition by…order by…) as row_id…where row_id<=n取排名前幾的資料,對于有些字段會對應多個值，而在建構資料名額時，一般隻會取常用的值進行呈現，不會窮盡所有值

select * from(
select  t.mobile, t.userid,
row_number() over (partition by t.userid order by t.REGDATE desc) as row_id
from
(--擷取注冊時登記的号碼
select e.mobile,u.userid,u.REGDATE from syscore_kudu.users u
inner join syscore_kudu.employee e on u.userid=e.userid
where trim(e.mobile) REGEXP "^[1]([3-9])[0-9]{9}$"
) t )t
where t.row_id=1
           

使用trim（）去除空值，并用正則比對電話号碼，一般涉及到電話号碼相關，一般都會存在空值

select e.mobile,u.userid,u.REGDATE from syscore_kudu.users u
inner join syscore_kudu.employee e on u.userid=e.userid
where trim(e.mobile) REGEXP "^[1]([3-9])[0-9]{9}$"
           

時間相關的函數，在涉及到使用者登入資訊相關的時候，常會與時間進行挂鈎

–使用substr(…,…,…)取時間相關 1-4表示取到年 1-7表示取到月 1-10表示取到天

–使用round（…,…）進行取整，描述取到小數點後幾位

–now()表示取目前時間下的時間

–year(now())表示取年

–取小時：hour()

–取天：day()

–取時間戳：unix_timestamp()

–months_sub(now(),1）取上一個月份

–months_sub(now(),6) 取近6個月

–year(years_sub(now(), 1)）取上一年

–years_add(now(),1) 取未來一年内的時間

–datediff(now(),…)<time 取近time天

–時間戳轉成标準時間：to_char（’’,‘yyyy-mm-dd’）

select  userid,count(distinct substr(sendTime,1,10)) '本年登入天數',
    count(UUID) '本年登入次數',
    round(count(UUID)/count(distinct substr(sendTime,1,10)),0) '日均登入次數',
    max(sendTime) '最近登陸時間',
    round(count(distinct substr(sendTime,1,10))/365,3) '登入天數占全年比重'
from 3d_point_kudu.userinfo 
where substr(sendTime,1,4) = cast(year(now()) as string) and length(sendTime)=19
group by userid;
           

IFNULL(expr1,expr2)，如果expr1不是NULL，IFNULL()傳回expr1，否則它傳回expr2，在建構資料名額和标簽時，常會遇到很多空值，而為了更好的展現，一般會用預設值對空值進行填充

select materialid,ifnull(productid,'沒有産品') from
pmc_kudu.designmaterial
           

聚合函數 求和sum() 計數count() 求平均avg()

#統計素材的拖拽資訊，近30天拖拽情況#
select materialid,
sum(dragTimes) as darg_cnt30 --近30天拖拽次數
from 3D_point_kudu.materialDragInfo 
where datediff(now(),sendtime )<=30
group by materialid
           

運用start with … connect by prior…進行周遊查找，在日常業務存儲中，對于多層關系常會用樹狀結構進行存儲，則在查找的時候就需要進行周遊，比如：素材的id是具有多層層級結構的，要找到他的父節點，就需要進行周遊

--公共定制庫的起始類别情況，起始categoryid='2123602'
SELECT categoryid,parentid,CATEGORYNAME,
sys_connect_by_path(CATEGORYNAME,'->') NAME
FROM pmc.categorynew t
START WITH t.categoryid = '2123602'
CONNECT BY t.parentid = prior t.categoryid  and  t.isdelete=0
           

使用left(…,…)函數從左取值，right(…,…)從右開始去規定範圍内的字元數表

select materialid,left(materialname,7),right(materialname,7) from
pmc_kudu.designmaterial
limit 10
           

将毫秒的字元串相減 1）秒以上部分用時間戳轉化，相減得到s機關 2）毫秒用right截取後，利用強制轉化為整數

select starttime,endtime,
(unix_timestamp(endtime)-unix_timestamp(starttime))*1000+
cast(right(endtime,length(endtime)-20) as int)-cast(right(starttime,length(starttime)-20) as int) ms
from performance 
           

使用if(expr1,expr2,expr3)可用來可以作為表達式用，也可在存儲過程中作為流程控制語句使用 如果 expr1 是TRUE，則傳回值為expr2; 否則傳回值為 expr3。IF() 的傳回值為數字值或字元串值，具體情況視其所在語境而定

select substr(createdate,1,7) as date_dl,
count(if(flag=0,true,null)) as xgt_cnt,--效果圖數
count(if(flag=0 and width<=800 ,true,null)) bq_cnt,--标清
count(if(flag=0 and width>800 and width<=1280,true,null)) gq_cnt,--高清
from 
(select  flag,createdate,width from pmc_kudu.queueok)b
group by  substr(createdate,1,7)
           

最後，這篇文章是基于我畢業不到一年的認知所寫的，有寫得不對的地方歡迎和我交流。因為自己認識的做資料産品經理的朋友也比較少，不太清楚别人的資料産品經理是什麼樣子的。是以有想一起學習成長的朋友可以加個qq：624488342 ，一起交流溝通哈！