sql汇总以及gp改造优化

一、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.user
ORDER 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.username
ORDER 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.username
ORDER 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改造

日期时间改造

SELECT
to_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_hour
FROM
radacct_time a

字符串拼接数据类型改造

SELECT
eg.semester,
ROUND(
 SUM(CAST(eg.credit as numeric) * cast(eg.point as numeric))/sum(cast(eg.credit as numeric)),2)as avg_credit_point
FROM dm_edu_grade_filter_r_semester eg
WHERE 1=1
AND cast(eg.credit as numeric)>0
AND SUBSTRING(eg.course_no,4,1)<>'9'
AND eg.outid in(SELECT outid FROM dim_own_org_student_type_1
WHERE
        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 num
FROM
    dm_m_rec_consume b
WHERE
    1 = 1
GROUP BY
    b.opdt
ORDER 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')q
FROM  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')q
FROM  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获取某个字符串在一个字符串中的位置
 
 

 

转载于:https://www.cnblogs.com/languid/p/10985809.html