22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE b
AS
SELECT dt,
count(distinct cookie) as uv
FROM data
WHERE dt>=date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),14)
group by dt
;
FROM
(
SELECT dt,
uv,
(select uv FROM b WHERE dt=date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1)) AS duv
FROM b
) AS t
SELECT dt,
uv,
(uv - duv) / luv AS rihuanbi
WHERE dt>=date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),7)
select t1.*, t2.cc as 上月cc
from t1
left join t2
on t1.aa = t2.aa
and t1.月份 = CONVERT(VARCHAR(7),DATEADD(MM, 1, t2.月份 + '-01'), 121)
SELECT dt,
uv,
(uv - duv) / luv AS rihuanbi
FROM
(
SELECT dt,
uv,
(
SELECT TOP 1
uv
FROM
(
SELECT dt,
COUNT(DISTINCT cookie) AS uv
FROM data
WHERE dt >= date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 14)
GROUP BY dt
) bb
WHERE dt = date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 1)
) AS duv
FROM
(
SELECT dt,
COUNT(DISTINCT cookie) AS uv
FROM data
WHERE dt >= date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 14)
GROUP BY dt
) b
) t
WHERE dt >= date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 7);