常用SQL集锦

去掉重复数据,只保留一条

DELETE FROM 表名
WHERE id NOT IN 
    (SELECT nd.i FROM
        (SELECT MAX(id) i FROM 表名  GROUP BY 重复字段  HAVING COUNT(*) > 1 
        UNION 
        SELECT  MAX(id) i  FROM 表名  GROUP BY 重复字段  HAVING COUNT(*) = 1) nd
    )

保留id较大的那一条,若MAX换成MIN则保留较小的那一条记录。

行转列 GROUP_CONCAT

函数默认分隔符是 ,

原有数据

实例执行后
语句:
SELECT name,GROUP_CONCAT(value [order by] [SEPARATOR '_']) from 表名 [where] group by name

实例:
select name,GROUP_CONCAT(value order by id desc SEPARATOR '_') as value from cap_dim where platform=1 and cate_name='机油' group by name

修改表字段的长度

规则:
alter table 表名 modify column 列名 类型(要修改的长度);
实例:
ALTER TABLE recommend.cap_sku MODIFY COLUMN tags varchar(2000);

分组获取第一条数据

name  score  type
张三    89     1
李四    58     1
王五    80     2
马六    35     2
周七    95     3

要求查询结果
name  score  type
张三    89     1
王五    80     2
周七    95     3

SQL为
select * from (
    select * from stu group by type, score desc
    ) as base
group by type

再举个SQL例子
 select url,shop_url from (
   select * from cap_sku where platform=1 and shop_name not like '%京东自营%' group by shop_url desc
    ) as base
 group by shop_url

计算某一张表,或者数据库的大小

下面的SQL是计算recommend库的2张表大小,如果去掉表就是整个库大小
结果是字节,所以除以1024是K,再除以1024是M,再除以1024是G
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA='recommend' AND TABLE_NAME in ('cap_sku','cap_review')

从2018年1月1日截至到现在,一共有多少秒,多少天,多少周,多少月

SQL 语句
语法为:TIMESTAMPDIFF(unit,datetime1,datetime2),
其中unit单位有如下几种,分别是:FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR。
现在是 2018年8月20日 10:00

以下语句分别是:秒,天,周,月
SELECT TIMESTAMPDIFF(SECOND, "2018-01-01 00:00:00",now())       19994311
SELECT TIMESTAMPDIFF(DAY, "2018-01-01 00:00:00",now())     231
SELECT TIMESTAMPDIFF(WEEK, "2018-01-01 00:00:00",now())   33
SELECT TIMESTAMPDIFF(WEEK, "2018-01-01 00:00:00",now())    7

计算活跃用户留存(次日,7日等)

select dt,
count(distinct cookie) as uv,
count ( distinct case when intv = 1 then cookie else null end ) as retain1_uv,
count ( distinct case when intv = 1 then cookie else null end )/ count(distinct cookie) as retain1_rate 
from 
( select 
a.dt , 
a.cookie ,
datediff(b.dt,a.dt) as intv
from 
( select distinct dt, deviceid as cookie 
from table
where dt>=date_sub('2018-09-20',1) and dt<='2018-11-14'
and xxx
) a 
left outer join 
( select distinct dt, deviceid as cookie 
from table
where dt>=date_sub('2018-09-20',1) and dt<='2018-11-14'
and xxx
) b on a.cookie= b.cookie 
) c 
group by dt;
结果为
日期            DAU   次日活跃   留存
2018-09-20      82      48      0.5853658537
2018-09-21      120     21      0.175
2018-09-22      273     16      0.05860805860805861
2018-09-23      761     20      0.026281208935611037
2018-09-24      1289    45      0.03491078355314197

计算新用户留存(次日,7日)

select dt, 
count(distinct cookie) as new_uv,
-- count ( distinct case when intv = 0 then cookie else null end ) as retain0_user ,
count ( distinct case when intv = 1 then cookie else null end ) as iretain1_newuv,
count ( distinct case when intv = 1 then cookie else null end )/ count(distinct cookie) as retain1_rate 
from 
( select 
a.dt , 
a.cookie ,
datediff(b.dt,a.dt) as intv
from 
( select distinct dt, upper(device_id) as cookie 
from table
where dt>=date_sub('${yyyy-mm-dd[-1]}',1) and dt<='${yyyy-mm-dd[-1]}' and is_new_user=1 
and app_key in (
xxx
) a 
left outer join 
( select distinct dt, upper(device_id) as cookie 
from table
where dt>=date_sub('${yyyy-mm-dd[-1]}',1) and dt<='${yyyy-mm-dd[-1]}'
and app_key in (xxx
) b on a.cookie= b.cookie 
) c 
group by dt

时间按月分组

select  date_format(dt, 'yyyyMM'),count(distinct device_id) as uv  from ods.o_p04_app_client_data_i WHERE dt >= '2015-01-01' AND dt<='2015-12-31' and app_key in ('wz_ios','wz_android')  group by date_format(dt, 'yyyyMM')

推荐阅读更多精彩内容

  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 117,181评论 15 132
  • 如果您的心中想到失败,您就失败! 如果您没有必胜的决心,绝无任何成就! 纵使您想要得到胜利i,只要浮现失败的字眼,...
    陈东Growth阅读 451评论 0 0
  • 手机没电了 世界突然清净了 我在想接着可以干些什么 想想就兴奋。
    留子尧阅读 84评论 0 2
  • 01、 其实他是一只鬼,一只刚刚夺人神志强行附在人身的恶鬼。 02、 他接管了这具身体,注视着这陌生的城市,他死了...
    离尘丶阅读 450评论 11 5
  • 花圃旁 绿树下 她与影共舞优雅 翩然月下 袅袅夜色中 静默书写着 永不分离的爱情童话
    神采飞扬MM阅读 100评论 12 11