hive求环比求大神帮忙

2018-05-08 02:49:46 1.5k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

;WITH 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
)
SELECT dt,
uv,
(uv - duv) / luv AS rihuanbi,
FROM
(
SELECT dt,
uv,
(select uv FROM b WHERE dt=date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1)) AS duv,
FROM b
) t
WHERE dt>=date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),7)

结果显示 cannot recognize input near 'FROM' '(' 'SELECT' in selection target

这是在hive上面运行的不知道hive上是否可以使用 with as 语句以及如果可以的话这样要怎么改感谢！

...全文

数据分析SQL环比增长率、同比增长率环比同比阅读详情

csrqql

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

csrqql 2018-05-10

打赏
举报

引用 15 楼 roy_88 的回复:

分两步试试 1步

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
;

2步

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)

可惜的是因为我这个运行环境只能用来取数，不能再建表了

csrqql 2018-05-10

打赏
举报

引用 13 楼 fkcqzwx 的回复:

不懂HIVE，纯从SQL来说，不需要用到with以及SQL Server2012才支持的LAG，直接派生表自联接，条件加上一个“t1.月份 = t2.月份 + 1”. MSSQL举个简单的例子，你可以自行转成相应的语法及日期处理函数。
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)

感谢~ 嗯其实我之前找了三种方法，最终采用的就是类似这个的，只是想再试试这里的不需要重新创建表，直接通过日期的关系来取当天和前天的uv是不是可行

中国风 2018-05-09

打赏
举报

分两步试试
1步

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

;

2步

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)

等待戈多12 2018-05-08

打赏
举报

不懂HIVE，纯从SQL来说，不需要用到with以及SQL Server2012才支持的LAG，直接派生表自联接，条件加上一个“t1.月份 = t2.月份 + 1”. MSSQL举个简单的例子，你可以自行转成相应的语法及日期处理函数。


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)

csrqql 2018-05-08

打赏
举报

引用 11 楼 sinat_28984567 的回复:

[quote=引用 10 楼 csrqql 的回复:] [quote=引用 5 楼 sinat_28984567 的回复:] [quote=引用 4 楼 csrqql 的回复:] [quote=引用 3 楼 sinat_28984567 的回复:] [quote=引用 2 楼 csrqql 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这样

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);

不行~ 显示 cannot recognize input near '(' 'SELECT' 'TOP' in expression specification hive 上面不需要top吧？[/quote] hive好像是用limit加上limit来实现top，如果不加top 会有问题，会返回一个结果集报错[/quote] 可是现在这是什么情况捏~~ [/quote] 把top换成limit+order by[/quote] 好像还是有问题。。。并且感觉这样的话也有点复杂。。。hive里是不能用with as 是么~[/quote] 对 hive 不太熟悉；其实with as在这也就是个临时表用到多处的意思[/quote] 好吧~~ 我再查查谢谢斑斑啦

二月十六 2018-05-08

打赏
举报

引用 10 楼 csrqql 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:] [quote=引用 4 楼 csrqql 的回复:] [quote=引用 3 楼 sinat_28984567 的回复:] [quote=引用 2 楼 csrqql 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这样

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);

csrqql 2018-05-08

打赏
举报

引用 5 楼 sinat_28984567 的回复:

[quote=引用 4 楼 csrqql 的回复:] [quote=引用 3 楼 sinat_28984567 的回复:] [quote=引用 2 楼 csrqql 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这样

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);

csrqql 2018-05-08

打赏
举报

引用 8 楼 xxzxwsx 的回复:

哦哦刚刚看错字了~~ 不过这个改过去后运行结果还是有问题。。差不多还是原来的问题，，惆怅

在路上_- 2018-05-08

打赏
举报

在路上_- 2018-05-08

打赏
举报

FROM 前边多了一个逗号

csrqql 2018-05-08

打赏
举报

引用 3 楼 sinat_28984567 的回复:

[quote=引用 2 楼 csrqql 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这样

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);

二月十六 2018-05-08

打赏
举报

引用 2 楼 csrqql 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 试试这样

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);

不行~ 显示 cannot recognize input near '(' 'SELECT' 'TOP' in expression specification hive 上面不需要top吧？[/quote] hive好像是用limit加上limit来实现top，如果不加top 会有问题，会返回一个结果集报错

csrqql 2018-05-08

打赏
举报

引用 6 楼 xxzxwsx 的回复:

FROM 前边多了一个逗号

哈？哪个地方捏好像没有啊~

csrqql 2018-05-08

打赏
举报

引用 1 楼 sinat_28984567 的回复:

试试这样

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);

不行~ 显示 cannot recognize input near '(' 'SELECT' 'TOP' in expression specification hive 上面不需要top吧？

二月十六 2018-05-08

打赏
举报

引用 4 楼 csrqql 的回复:

[quote=引用 3 楼 sinat_28984567 的回复:] [quote=引用 2 楼 csrqql 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:] 试试这样

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);

二月十六 2018-05-08

打赏
举报

试试这样

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);

hive求环比 求大神帮忙

相关推荐

hive求环比求大神帮忙