一、sql汇总

1.对xxxx@zzz.com 这种邮箱地址截取后缀

select right('zhsan@163.com',length('zhsan@163.com')-position('@' in 'zhsan@163.com'));

切割字符串，确定字符串长度，减去前半部分长度。

 

2.

：B表

 

：A表

 

：C表

用一条sql得到 c表的结果

SELECT id,sum(num) FROM(SELECT * from a UNION all SELECT * FROM b) as tmp GROUP BY id;

 

3.

user   name

1          a

1          a

1          b

2          c

2          c

2          d

想要分组以后得到

user   name   count

1          a          2

2          c          2

SELECT t1.* FROM (Select user,name,COUNT(1) AS count from T GROUP BY user,name)t1  INNER JOIN(SELECT t.user,MAX(t.count) AS count FROM (Select user,name,COUNT(1) AS count from T GROUP BY user,name)t  GROUP BY t.user)t2 ON t2.count = t1.count AND t2.user = t1.userORDER BY t1.user

 

 

应用到实际的查询中，username对应的mac地址中，groupby以后每组最多的mac地址的count

SELECT t1.* FROM (Select username,callingstationid,COUNT(1) AS count from radacctlog_2019_03 GROUP BY username,callingstationid)t1 INNER JOIN(SELECT t.username,MAX(t.count) AS count FROM  (Select username,callingstationid,COUNT(1) AS count from radacctlog_2019_03 GROUP BY username,callingstationid)t GROUP BY t.username)t2 ON t2.count = t1.count AND t2.username = t1.usernameORDER BY t1.username

 

 

SELECT t1.* FROM (Select username,callingstationid,COUNT(1) AS count from radacctlog_2019_03 GROUP BY username,callingstationid)t1 INNER JOIN(SELECT t.username,MAX(t.count) AS count FROM (Select username,callingstationid,COUNT(1) AS count from radacctlog_2019_03 GROUP BY username,callingstationid)t GROUP BY t.username)t2 ON t2.count = t1.count AND t2.username = t1.usernameORDER BY t1.username

 

4.

对某一天出现的数据行进行筛选

 

SELECT * FROM `radacctlog_2019_03` where acctstarttime <='2019-03-12 00:00:00'  and acctstoptime> '2019-03-13 00:00:00'  or acctstarttime BETWEEN'2019-03-12 00:00:00' and '2019-03-13 00:00:00' or  acctstoptime  BETWEEN'2019-03-12 00:00:00' and '2019-03-13 00:00:00';#指点时间段

 

对大于一天的数据进行筛选

SELECT * FROM `radacctlog_2019_03` WHERE acctstoptime-acctstarttime>86400#上行时间大于一天的

 

 

5.计算一张表中 挂科数大于二的同学的平均成绩

一条sql（逆向）：

SELECT name,AVG(score),sum(score<60) as gks FROM result GROUP BY name HAVING gks>=2;

 

正常逻辑：

SELECT name,avg(score) from result WHERE name in  (SELECT a.name FROM(SELECT  name, score FROM result WHERE score<60)a GROUP BY a.name HAVING count(a.score)>=2) GROUP BY name

 

 

二、mysql迁移到gp改造

日期时间改造

SELECTto_date(to_char(a.acctstarttime, 'YYYY-MM-DD'),'YYYY-MM-DD') acctstarttime,to_date(to_char(a.acctstoptime, 'YYYY-MM-DD'),'YYYY-MM-DD') acctstoptime,EXTRACT(dow FROM a.acctstoptime),IF(    EXTRACT(HOUR FROM a.acctstarttime)=0.24,    EXTRACT(HOUR FROM a.acctstarttime)    )start_hourFROMradacct_time a

字符串拼接数据类型改造

SELECTeg.semester,ROUND( SUM(CAST(eg.credit as numeric) * cast(eg.point as numeric))/sum(cast(eg.credit as numeric)),2)as avg_credit_pointFROM dm_edu_grade_filter_r_semester egWHERE 1=1AND cast(eg.credit as numeric)>0AND SUBSTRING(eg.course_no,4,1)<>'9'AND eg.outid in(SELECT outid FROM dim_own_org_student_type_1WHERE        edu_status like (('%'||'1-')||'%'))        GROUP BY eg.semester

对sql count（distinct）优化改造

SELECT    b.opdt AS datetime,    SUM (b.opfare) AS sums,    COUNT (DISTINCT(b.outid)) AS numFROM    dm_m_rec_consume bWHERE    1 = 1GROUP BY    b.opdtORDER BY    b.opdt;

SELECT c.opdt as datetime,    sum(c.opfare) as sums,    count(*) as num     FROM    (SELECT b.opdt,b.opfare,b.outid     FROM dm_m_rec_consume b    GROUP BY b.opdt,b.opfare,b.outid)c    GROUP BY    c.opdt

 

其他一些gp中时间函数的使用

SELECT EXTRACT(HOUR FROM e.q)g FROM(SELECT to_date(to_char(a.admission_date,'YYYY-MM-DD'),'YYYY-MM-DD')qFROM  dim_own_org_student_type_1 a)e#获取小时SELECT EXTRACT(dow FROM e.q)g FROM(SELECT to_date(to_char(a.admission_date,'YYYY-MM-DD'),'YYYY-MM-DD')qFROM  dim_own_org_student_type_1 a)e#获取周几select EXTRACT(day from now()) #日期select EXTRACT(doy from now()) #一年的第几天select EXTRACT(month from now())#月份select EXTRACT(minute from now())#分钟select EXTRACT(week from now())#一年的第几周select EXTRACT(year from now())#年份select date_part('min','2015-01-15 17:05'::timestamp - '2015-01-13 15:01'::timestamp);#时间差SELECT to_date('05 Dec 2000','DD MonYYYY')#字符串转化为日期Select to_char(-125.8,'999D99S')#把数字转化为字符串SELECT to_char(INTERVAL'15h 2m 12s','HH24:MI:SS')#把时间间隔转化为时间SELECT to_char(125,'999')#把整数转化为字符串SELECT to_char(125.8::real,'999D9') #把实数/双精度数转化为字符串SELECT to_timestamp('05 Dec 2000','DD Mon YYYY') #把字符串转化为时间戳SELECT to_number('-12,454.8-','99G999D9S')#把字符串转化为数字 SELECT POSITION('a' in 'bcad' )#=>3获取某个字符串在一个字符串中的位置