sqlserver 求环比首次发帖。。萌新求大神指点~~

csrqql 2018-04-26 11:21:15

在sqlserver 上根据已有的数据求日环比和周环比换了几个思路一直有问题萌

用到的table_uv 表里面是每天的uv记录
==================== 1 ==========================

select
dt,
uv,
(uv-duv)/luv as rihuanbi,
(uv-wuv)/uv as zhouhuanbi
from
(select
dt,
uv,
(select uv from b where DateDiff(day,b.dt,dt)=1) as duv,
(select uv from b where DateDiff(day,b.dt,dt)=7) as wuv,
from
(
select
dt,
uv
from table_uv
WHERE DateDiff(dd,dt,convert(varchar,getdate(),112))<=14
order by dt desc
)b
)
WHERE DateDiff(dd,dt,convert(varchar,getdate(),112))<=7

不知道这个问题是不是出在第二个select 语句中这样select 能取出想要的duv （前一天的uv）
和wuv(7天前的uv）值来吗？

==================================2================================
第二个想法是联结表

select
a.dt as dt,
a.uv as uv,
(a.uv-b.uv)/b.uv as rihuanbi,
(a.uv-c.uv)/c.uv as zhouhuanbi
from
(select
dt,
uv
from table_uv
where DateDiff(dd,dt,convert(varchar,getdate(),112))<=7
) a
join
(select
dt,
uv
from table_uv
where 1<=DateDiff(dd,dt,convert(varchar,getdate(),112))<8
) b on DateDiff(dd,b.dt,a.dt)=1
join
(select
dt,
uv
from table_uv
where 7<DateDiff(dd,dt,convert(varchar,getdate(),112))<=14
) c on DateDiff(dd,c.dt,a.dt)=7

然后结果还是出问题： “<”附近有语法错误找不出问题。。。。惆怅

===================3 ==============================

这里我想用lag函数
select
dt as dt,
uv as uv,
(uv-duv)/duv as rihuanbi,
(uv-wuv)/wuv as zhouhuanbi
from
(
select
dt,
uv,
lag(uv,1) over(order by dt) as duv,
lag(uv,7) over(order by dt) as wuv
from table_uv
where DateDiff(dd,dt,convert(varchar,getdate(),112))<=14
)
where DateDiff(dd,dt,convert(varchar,getdate(),112))<=7

然鹅还是不行啊啊显示关键字 'where' 附近有语法错误。

非常郁闷求大神帮忙！！！！感谢！！！！！

...全文

768 27 打赏收藏转发到动态举报

写回复

用AI写文章

27 条回复

切换为时间正序

请发表友善的回复…

发表回复

csrqql 2018-04-27

打赏
举报

引用 26 楼 sinat_28984567 的回复:

[quote=引用 25 楼 csrqql 的回复:] [quote=引用 24 楼 sinat_28984567 的回复:] [quote=引用 22 楼 csrqql 的回复:] [quote=引用 20 楼 sinat_28984567 的回复:] 你为啥这么执着这么麻烦的写法……看起来都很麻烦

我也不想的呀。。。但是 ;WITH b AS (SELECT dt, uv FROM table_uv WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14 ) SELECT dt, uv, (uv - duv) / luv AS rihuanbi, (uv - wuv) / uv AS zhouhuanbi FROM ( SELECT dt, cast(uv as float) as uv, ( SELECT TOP 1 cast(uv as float) FROM b WHERE DATEDIFF(DAY, b.dt, dt) = 1 ) AS duv, ( SELECT TOP 1 cast(uv as float) FROM b WHERE DATEDIFF(DAY, b.dt, dt) = 7 ) AS wuv FROM b ) t WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7; 这个吧它显示： sql中字段别名不支持特殊字符，包括（@、#、￥、%等）。然鹅我带着放大镜也木有找到特殊字符啊啊[/quote] 试试21#的代码[/quote] 那个。。斑斑可以说详细点咩~~ 我是最近两天才接触sqlserver的不太明白21#的代码是什么意思~ 网上也木有查到具体的。。[/quote] 就是执行这句代码

SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               ROUND(CONVERT(FLOAT, (a.uv - b.uv) / b.uv), 2)
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               ROUND(CONVERT(FLOAT, (a.uv - c.uv) / c.uv), 2)
           ELSE
               0
       END AS 周环比
FROM
(
    SELECT dt,
           ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
    GROUP BY dt
) a
    LEFT JOIN
    (
        SELECT dt,
               ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
        FROM table_uv
        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
        GROUP BY dt
    ) b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN
    (
        SELECT dt,
               ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
        FROM table_uv
        WHERE 7 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
        GROUP BY dt
    ) c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7;

21#那个上半部分代码是测试用的数据，然后测试数据对应的结果也出来了，这个测试结果对吗？如果对就执行上边的这段代码，如果上边这段select代码还出不来结果，那楼主把你真实数据的一部分，以及这部分对应的结果写出来，再写语句。如果上边这个测试用的数据和结果都不对，那楼主按照这个测试数据给出正确的结果，再改这个语句……[/quote] 好的~~ 太感谢斑斑了~~

二月十六 2018-04-27

打赏
举报

引用 25 楼 csrqql 的回复:

[quote=引用 24 楼 sinat_28984567 的回复:] [quote=引用 22 楼 csrqql 的回复:] [quote=引用 20 楼 sinat_28984567 的回复:] 你为啥这么执着这么麻烦的写法……看起来都很麻烦

SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               ROUND(CONVERT(FLOAT, (a.uv - b.uv) / b.uv), 2)
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               ROUND(CONVERT(FLOAT, (a.uv - c.uv) / c.uv), 2)
           ELSE
               0
       END AS 周环比
FROM
(
    SELECT dt,
           ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
    GROUP BY dt
) a
    LEFT JOIN
    (
        SELECT dt,
               ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
        FROM table_uv
        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
        GROUP BY dt
    ) b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN
    (
        SELECT dt,
               ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv
        FROM table_uv
        WHERE 7 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
        GROUP BY dt
    ) c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7;

csrqql 2018-04-27

打赏
举报

引用 24 楼 sinat_28984567 的回复:

[quote=引用 22 楼 csrqql 的回复:] [quote=引用 20 楼 sinat_28984567 的回复:] 你为啥这么执着这么麻烦的写法……看起来都很麻烦

二月十六 2018-04-27

打赏
举报

引用 22 楼 csrqql 的回复:

[quote=引用 20 楼 sinat_28984567 的回复:] 你为啥这么执着这么麻烦的写法……看起来都很麻烦

csrqql 2018-04-27

打赏
举报

引用 20 楼 sinat_28984567 的回复:

你为啥这么执着这么麻烦的写法……看起来都很麻烦

二月十六 2018-04-27

打赏
举报

你为啥这么执着这么麻烦的写法……看起来都很麻烦

二月十六 2018-04-27

打赏
举报

按照之前的测试数据写的，后边有个等号改动，如果还不对，建议楼主再给出测试数据以及对应的结果

--测试数据

if not object_id(N'table_uv') is null

    drop table table_uv

Go

Create table table_uv([dt] Date,[uv] int)

Insert table_uv

select '2018-04-27',80 union all

select '2018-04-26',70 union ALL

select '2018-04-25',40 union all

select '2018-04-24',40 union all

select '2018-04-23',30 union ALL

select '2018-04-22',25 union all

select '2018-04-21',20 union all

select '2018-04-20',10

Go

--测试数据结束



SELECT a.*,

       CASE

           WHEN b.dt IS NOT NULL THEN

               ROUND(CONVERT(FLOAT, (a.uv - b.uv) / b.uv), 2)

           ELSE

               0

       END AS 日环比,

       CASE

           WHEN c.dt IS NOT NULL THEN

               ROUND(CONVERT(FLOAT, (a.uv - c.uv) / c.uv), 2)

           ELSE

               0

       END AS 周环比

FROM

(

    SELECT dt,

           ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv

    FROM table_uv

    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7

    GROUP BY dt

) a

    LEFT JOIN

    (

        SELECT dt,

               ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv

        FROM table_uv

        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))

              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8

        GROUP BY dt

    ) b

        ON DATEDIFF(DAY, b.dt, a.dt) = 1

    LEFT JOIN

    (

        SELECT dt,

               ROUND(SUM(CAST((uv) AS FLOAT)), 2) AS uv

        FROM table_uv

        WHERE 7 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))

              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14

        GROUP BY dt

    ) c

        ON DATEDIFF(DAY, c.dt, a.dt) = 7;

csrqql 2018-04-27

打赏
举报

引用 18 楼 sinat_28984567 的回复:

[quote=引用 17 楼 csrqql 的回复:] [quote=引用 16 楼 sinat_28984567 的回复:]

SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - b.uv)) / b.uv
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - c.uv)) / c.uv
           ELSE
               0
       END AS 周环比
FROM 
(
    SELECT dt,
   cast(uv as bigint) as uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
) a
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
    )	b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
    )	c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7

感谢斑斑！！[/quote] 如果没问题的话记得结贴哦[/quote] 嗯还有最后一个问题这个sql运行出来环比部分都是 0 我又用round 保留了一下小数位如下 SELECT a.*, CASE WHEN b.dt IS NOT NULL THEN round(CONVERT(float, (a.uv - b.uv)/ b.uv),2) ELSE 0 END AS 日环比, CASE WHEN c.dt IS NOT NULL THEN round(CONVERT(float, (a.uv - c.uv)/c.uv),2) ELSE 0 END AS 周环比 FROM ( SELECT dt, round(sum(cast((uv) as float)),2) as uv FROM table_uv WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7 group by dt ) a LEFT JOIN ( SELECT dt, round(sum(cast((uv) as float)),2) as uv FROM table_uv WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8 group by dt ) b ON DATEDIFF(DAY, b.dt, a.dt) = 1 LEFT JOIN ( SELECT dt, round(sum(cast((uv) as float)),2) as uv FROM table_uv WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14 group by dt ) c ON DATEDIFF(DAY, c.dt, a.dt) = 7 可是结果还是 0 像这样日环比周环比 0 0 0 0 -0 0 -0 -0 0 -0 0 0

二月十六 2018-04-26

打赏
举报

引用 17 楼 csrqql 的回复:

[quote=引用 16 楼 sinat_28984567 的回复:]

SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - b.uv)) / b.uv
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - c.uv)) / c.uv
           ELSE
               0
       END AS 周环比
FROM 
(
    SELECT dt,
   cast(uv as bigint) as uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
) a
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
    )	b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
    )	c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7

感谢斑斑！！[/quote] 如果没问题的话记得结贴哦

csrqql 2018-04-26

打赏
举报

引用 16 楼 sinat_28984567 的回复:

SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - b.uv)) / b.uv
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - c.uv)) / c.uv
           ELSE
               0
       END AS 周环比
FROM 
(
    SELECT dt,
   cast(uv as bigint) as uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
) a
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
    )	b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
    )	c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7

感谢斑斑！！

二月十六 2018-04-26

打赏
举报

SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - b.uv)) / b.uv
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               CONVERT(DECIMAL(18, 2), (a.uv - c.uv)) / c.uv
           ELSE
               0
       END AS 周环比
FROM 
(
    SELECT dt,
   cast(uv as bigint) as uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7
) a
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8
    )	b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN 
(
        SELECT dt,
               cast(uv as bigint) as uv
        FROM table_uv
        WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112))
              AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
    )	c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7

csrqql 2018-04-26

打赏
举报

引用 14 楼 sinat_28984567 的回复:

数据类型 varchar 和 varchar 在 subtract 运算符中不兼容 uv是什么类型的字段，不行的话强制转换一下在计算。哪种方法都可以，但是这个uv字段类型的必须的先解决

SELECT a.*, CASE WHEN b.dt IS NOT NULL THEN CONVERT(DECIMAL(5, 2), (a.uv - b.uv)) / b.uv ELSE 0 END AS 日环比, CASE WHEN c.dt IS NOT NULL THEN CONVERT(DECIMAL(5, 2), (a.uv - c.uv)) / c.uv ELSE 0 END AS 周环比 FROM ( SELECT dt, cast(uv as bigint) as uv FROM table_uv WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7 ) a LEFT JOIN ( SELECT dt, cast(uv as bigint) as uv FROM table_uv WHERE 1 <= DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) < 8 ) b ON DATEDIFF(DAY, b.dt, a.dt) = 1 LEFT JOIN ( SELECT dt, cast(uv as bigint) as uv FROM table_uv WHERE 7 < DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) AND DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14 ) c ON DATEDIFF(DAY, c.dt, a.dt) = 7 嗯这次倒是没有提醒sql出错然而结果没有出来数据出来：将 bigint 转换为数据类型 numeric 时出现算术溢出错误。可是我查了一下这个好像是长度的问题但是bigint也很长了啊。。。。同时还试了 int float numeric 。。。都不行

二月十六 2018-04-26

打赏
举报

数据类型 varchar 和 varchar 在 subtract 运算符中不兼容 uv是什么类型的字段，不行的话强制转换一下在计算。哪种方法都可以，但是这个uv字段类型的必须的先解决

csrqql 2018-04-26

打赏
举报

不知道是不是运行环境的问题结果还是显示为：数据类型 varchar 和 varchar 在 subtract 运算符中不兼容以及请问斑斑~ 这样的表结构和取数逻辑下，另外两种方法还可行么~~

引用 12 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([dt] Date,[uv] int)
Insert #T
select '2018-01-08',100 union all
select '2018-01-07',80 union all
select '2018-01-06',70 union all
select '2018-01-05',40 union all
select '2018-01-04',40 union all
select '2018-01-03',30 union all
select '2018-01-02',20 union all
select '2018-01-01',10
Go
--测试数据结束
SELECT a.*,
       CASE
           WHEN b.dt IS NOT NULL THEN
               CONVERT(DECIMAL(5, 2), (a.uv - b.uv)) / b.uv
           ELSE
               0
       END AS 日环比,
       CASE
           WHEN c.dt IS NOT NULL THEN
               CONVERT(DECIMAL(5, 2), (a.uv - c.uv)) / c.uv
           ELSE
               0
       END AS 周环比
FROM #T a
    LEFT JOIN #T b
        ON DATEDIFF(DAY, b.dt, a.dt) = 1
    LEFT JOIN #T c
        ON DATEDIFF(DAY, c.dt, a.dt) = 7;

二月十六 2018-04-26

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([dt] Date,[uv] int)

Insert #T

select '2018-01-08',100 union all

select '2018-01-07',80 union all

select '2018-01-06',70 union all

select '2018-01-05',40 union all

select '2018-01-04',40 union all

select '2018-01-03',30 union all

select '2018-01-02',20 union all

select '2018-01-01',10

Go

--测试数据结束

SELECT a.*,

       CASE

           WHEN b.dt IS NOT NULL THEN

               CONVERT(DECIMAL(5, 2), (a.uv - b.uv)) / b.uv

           ELSE

               0

       END AS 日环比,

       CASE

           WHEN c.dt IS NOT NULL THEN

               CONVERT(DECIMAL(5, 2), (a.uv - c.uv)) / c.uv

           ELSE

               0

       END AS 周环比

FROM #T a

    LEFT JOIN #T b

        ON DATEDIFF(DAY, b.dt, a.dt) = 1

    LEFT JOIN #T c

        ON DATEDIFF(DAY, c.dt, a.dt) = 7;

csrqql 2018-04-26

打赏
举报

引用 10 楼 sinat_28984567 的回复:

dt platform uv pv 2018-01-01 pc 1884953 3940 2018-01-01 m 1884953 3940 2018-01-02 pc 1884953 3940 2018-01-02 m 1884953 3940 这个对应的结果是什么？

希望是这样的： dt uv 日环比周环比 2018-01-08 a (a-b)/b (a-h)/h 2018-01-07 b (b-c)/c 2018-01-06 c 2018-01-05 。。。 2018-01-01 h （因为我前面给的表里数据是随便写的实际记录是从开始建表到当前日期的前一天每天都有记录

二月十六 2018-04-26

打赏
举报

dt platform uv pv 2018-01-01 pc 1884953 3940 2018-01-01 m 1884953 3940 2018-01-02 pc 1884953 3940 2018-01-02 m 1884953 3940 这个对应的结果是什么？

csrqql 2018-04-26

打赏
举报

引用 6 楼 sinat_28984567 的回复:

额，楼主你那个代码里边我没细看，只是把表面有问题的改了改，如果要彻底解决问题建议楼主列出表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

嗯嗯~~ 源表很简单字段也就是 dt,platform,uv,pv 差不多是这样的 dt platform uv pv 2018-01-01 pc 1884953 3940 2018-01-01 m 1884953 3940 2018-01-02 pc 1884953 3940 2018-01-02 m 1884953 3940 (差不多长这样子因为表不是我建的。。然后木有建表语句）我想取最近7天每天的uv以及uv日环比、周环比数据源类型是sqlserver sql语句运行环境也是... 因为之前主要用hive写然后在sqlserver 这里各种出问题还非常懵逼。。。

二月十六 2018-04-26

打赏
举报

第一个这样改改试试

;WITH b
AS (SELECT dt,
           uv
    FROM table_uv
    WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 14
   )
SELECT dt,
       uv,
       (uv - duv) / luv AS rihuanbi,
       (uv - wuv) / uv AS zhouhuanbi
FROM
(
    SELECT dt,
           uv,
           (
               SELECT TOP 1 uv FROM b WHERE DATEDIFF(DAY, b.dt, dt) = 1
           ) AS duv,
           (
               SELECT TOP 1 uv FROM b WHERE DATEDIFF(DAY, b.dt, dt) = 7
           ) AS wuv
    FROM b
) t
WHERE DATEDIFF(dd, dt, CONVERT(VARCHAR, GETDATE(), 112)) <= 7;

后边两个得给出具体表结构和数据了

csrqql 2018-04-26

打赏
举报

第一个试了一下显示为 SQLServerException: 对象名 'b' 无效。（一脸懵逼）第二个和第三个都是 SQLServerException: 数据类型 varchar 和 varchar 在 subtract 运算符中不兼容。意思是对减法不兼容？，，，难道是前面select做环比运算时候的影响？感觉没道理啊~~ 辛苦斑斑~