去掉重復(fù)數(shù)據(jù)沟饥，只保留一條
DELETE FROM 表名
WHERE id NOT IN
(SELECT nd.i FROM
(SELECT MAX(id) i FROM 表名 GROUP BY 重復(fù)字段 HAVING COUNT(*) > 1
UNION
SELECT MAX(id) i FROM 表名 GROUP BY 重復(fù)字段 HAVING COUNT(*) = 1) nd
)
保留id較大的那一條个粱，若MAX換成MIN則保留較小的那一條記錄。
行轉(zhuǎn)列 GROUP_CONCAT
函數(shù)默認(rèn)分隔符是 技潘，
原有數(shù)據(jù)
實(shí)例執(zhí)行后
語句:
SELECT name,GROUP_CONCAT(value [order by] [SEPARATOR '_']) from 表名 [where] group by name
實(shí)例:
select name,GROUP_CONCAT(value order by id desc SEPARATOR '_') as value from cap_dim where platform=1 and cate_name='機(jī)油' group by name
修改表字段的長(zhǎng)度
規(guī)則：
alter table 表名 modify column 列名 類型(要修改的長(zhǎng)度);
實(shí)例:
ALTER TABLE recommend.cap_sku MODIFY COLUMN tags varchar(2000);
分組獲取第一條數(shù)據(jù)
name score type
張三 89 1
李四 58 1
王五 80 2
馬六 35 2
周七 95 3
要求查詢結(jié)果
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
再舉個(gè)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
計(jì)算某一張表舟扎，或者數(shù)據(jù)庫的大小
下面的SQL是計(jì)算recommend庫的2張表大小分飞，如果去掉表就是整個(gè)庫大小
結(jié)果是字節(jié)悴务，所以除以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日截至到現(xiàn)在讯檐，一共有多少秒羡疗，多少天，多少周别洪，多少月
SQL 語句
語法為：TIMESTAMPDIFF(unit,datetime1,datetime2)叨恨，
其中unit單位有如下幾種，分別是：FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR挖垛。
現(xiàn)在是 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
計(jì)算活躍用戶留存(次日送矩，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;
結(jié)果為
日期 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
計(jì)算新用戶留存(次日，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
時(shí)間按月分組
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')
