SQL常用用法相关笔记

SQL常用用法相关笔记
1)、----CAST和CONVERT的用法
SQL中的cast和convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式。
CAST和CONVERT提供相似的功能，只是语法不同。
在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。
使用 CAST：
CAST ( expression AS data_type )
使用 CONVERT：
CONVERT (data_type[(length)], expression [, style])

2)、-----修改表test的字段 a 为 a1 类型为 int
ALTER TABLE test_change CHANGE a a1 INT;
ALTER TABLE dm_pquser_exception_detail_d CHANGE id id string;

ALTER TABLE lte_qcell_grid_information CHANGE floor floor varchar(100);
alter table lte_qcell_grid_information MODIFY COLUMN floor varchar(100)

ALTER TABLE lte_qcell_grid_information change COLUMN floor floor varchar(100);

3)、-----hive sql增加字段
alter table dm_highrail_sector_lte_new_d add columns(kqi_httpwebrspdelay bigint,kqi_httpwebcmpltdelay bigint,kqi_thrput bigint,kqi_stallfrequ bigint,kqi_imsentrate bigint)

4)、-----gbase sql增加字段
alter table dm_poor_ne_1800_index_d add app_score double

5)、----查看分区
show partitions lte_netmaxl_nbi_covergrid;

6)、----删除表分区
alter table cfg_sector_c drop partition(p_provincecode=510000,p_date='2017-05-01')

7)、----排序
order by
按clttime降序排序
order by clttime desc
按msisdn分组，在分组内按clttime降序排序 ，别名给num
row_number() over(partition by msisdn order by clttime desc) as num
row_number() over(partition by msisdn order by clttime desc) as num

8)、---rand()取随机数
select rand()*10 AS flag_rand from aggr_pl_abnormal_day limit 10;
floor是把rand()取随机数取整(向下取整)
select cast(floor(rand()*10) AS int) AS flag_rand from aggr_pl_abnormal_day limit 10;
ceil是把rand()随机数向上取整

9)、----按天删除表的数据
delete dm_base_sector_c_d where day='2017-12-05';

10)、---字符串截取函数，截取imsi从第一位到最后一位
substr(imsi,1,5)，
例如:截取月份：2017-10-10
select substr('2017-10-10',6,2); 返回10

11)、---coalesce取第一个不为空的值
select coalesce(null,8,9);

12)、---day函数，计算天数
比如：select day('2017-09-27'),结果为：27

13)、---在sql中获取系统实时的时间
select from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as insert_time

14)、---修改mysql数据库（gbae数据库）的表的字段值
update data_board set state=1 where tablename='dm_mob_inter_app_video_ci_4g_stat_d' and day='2017-09-20';

15)、---修复表分区
msck repair table dm_mdn_imsi_ternimal

16)、----UDF注册的使用:
-- 栅格id及x,y偏移量转经纬度
create temporary function MultiGridToLonLat as 'com.gstools.impala.MultiGridToLonLat';
MultiGridToLonLat（RegionID，X_Offset，Y_Offset,100）//100为栅格的大小

-- 标准经纬度转高德经纬度
create temporary function GDLonlat as 'com.gstools.impala.GDLonlat';
GDLonlat(longitude_left_up, latitude_left_up) as left_up,
GDLonlat(longitude_right_down, latitude_right_down) as right_down,
cast(split(left_up,',')[0] as float) as longitude_left_up,
cast(split(left_up,',')[1] as float) as latitude_left_up,
cast(split(right_down,',')[0] as float) as longitude_right_down,
cast(split(right_down,',')[1] as float) as latitude_right_down,

-- 经纬度转栅格id及x,y偏移量
create temporary function MultiLonLatToGrid as 'com.gstools.impala.MultiLonLatToGrid';
-- 计算两点距离
create temporary function LonLatDistance as 'com.gstools.impala.LonLatDistance';

17)、----栅格超过20米的判断
abs(left_up_longitude-right_down_longitude)>0.0005
select
count(1)
from dm_plan_grid_agps_d where p_date='2017-04-28' and abs(left_up_longitude-right_down_longitude)>0.0005;

18)、----排查数据重复出自那个表， 检查主键是否唯一，执行改sql，显示0条，则表示主键唯一，否则主键不唯一
video_ci_lte是小区的表 ：
select
enodebid,
cellid,
count(1)
from video_ci_lte where p_provincecode=510000 and reportdate='2017-10-31'
group by enodebid,cellid
having count(1)>1

转载于:https://www.cnblogs.com/hymmiaomiao/p/8691353.html