常用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')
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249

推荐阅读更多精彩内容

  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 134,049评论 18 139
  • MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,632评论 5 116
  • 如果您的心中想到失败,您就失败! 如果您没有必胜的决心,绝无任何成就! 纵使您想要得到胜利i,只要浮现失败的字眼,...
    陈东Growth阅读 745评论 0 0
  • 手机没电了 世界突然清净了 我在想接着可以干些什么 想想就兴奋。
    留子尧阅读 248评论 0 2
  • 01、 其实他是一只鬼,一只刚刚夺人神志强行附在人身的恶鬼。 02、 他接管了这具身体,注视着这陌生的城市,他死了...
    离尘丶阅读 669评论 11 5