开发者社区> 问答> 正文

查找错误记录(SQL)

我有一个表,结构如下。
create table t1 (

日期 date,
数据 double precision,
月累 double precision,
年累 double precision

)
表中的月累,和年累,是通过当日数据加昨日月累,年累所得。
当表中数据积累到一定量时,需要找出错误的,月累和年累。SQL语句怎么写。谢谢。

展开
收起
computerlover 2015-12-22 20:20:04 4729 0
3 条回答
写回答
取消 提交回答
  • 谢谢了,下面是postgresql 的SQL写法。

    -- p1 昨天的日期
    -- p2 当月统计数据范围内,上一条月累加值
    -- p3 当年统计数据范围内,上一条年累加值
    -- p4 当月统计数据范围内,第一条月累加值
    -- p5 当月统计数据范围内,第一条日值
    -- p6 当年统计数据范围内,第一条年累加值
    -- p7 当年统计数据范围内,第一条日值

    select * from
    (

    select v_d, data, month, year, 
    lag(v_d, 1) over (order by v_d) p1, 
    lag(month, 1) over (partition by extract(month from v_d) order by v_d) p2,
    lag(year, 1) over (partition by extract(year from v_d) order by v_d) p3,
    first_value(data) over(partition by extract(month from v_d) order by v_d) p4, 
    first_value(month) over(partition by extract(month from v_d) order by v_d) p5,
    first_value(data) over(partition by extract(year from v_d) order by v_d) p6, 
    first_value(year) over(partition by extract(year from v_d) order by v_d) p7 
    from tbl)

    t
    where v_d - p1 <>1
    or
    p4 <> p5
    or
    p6 <> p7
    or
    p2+data <> month
    or
    p3+data <> year;


    不知道这种写法对不对。在这个简单例子里可以得到期望结果。

    sum(data) over (partition by extract(month from v_d) order by v_d) as 计算月累,
    如果记录里年不都是一年,结果就不对。
    希望大拿帮助一下,解释partition by extract(month from v_d),谢谢了。
    select * from
    (

    select v_d, data, 
    month,
    sum(data) over (partition by extract(month from v_d) order by v_d) as 计算月累,
    year,
    sum(data) over (partition by extract(year from v_d) order by v_d) as 计算年累,
    lag(v_d, 1) over (order by v_d) p1
    from tbl

    ) t
    where v_d - p1 <>1
    or
    month <> 计算月累
    or
    year <> 计算年累

    2019-07-17 18:20:41
    赞同 展开评论 打赏
  • 一个PGer

    --我的理解昨天应该是指上一个日期,不一定连续
    --以下是PostgreSQL数据库的实现(仅供参考,没做实际数据测试)
    SELECT *
    FROM (SELECT *,

               lead(月累, 1, 0) over w AS last_month,
               lead(年累, 1, 0) over w AS last_year
          FROM t1 window w AS(PARTITION BY to_char(日期,'yyyy') ORDER BY 日期 DESC)) t

    WHERE t.last_month + 数据 != t.月累

         OR t.last_year + 数据 != t.年累;
    2019-07-17 18:20:41
    赞同 1 展开评论 打赏
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.
    假设你用的是PostgreSQL数据库。
    问题可能出在两处
    1. 缺失日期
    2. 数据错误
    月起始值错误
    年起始值错误
    月累加值错误
    年累加值错误
    
    pipeline=# create table tbl(v_d date, data numeric, month numeric, year numeric);
    CREATE TABLE
    pipeline=# insert into tbl values ('2015-01-01', 100, 100, 100);
    INSERT 0 1
    pipeline=# insert into tbl values ('2015-01-02', 200, 300, 300);
    INSERT 0 1
    pipeline=# insert into tbl values ('2015-01-04', 200, 500, 500);
    INSERT 0 1
    
    pipeline=# select v_d, data, month, year, 
    lag(v_d, 1) over (order by v_d) p1, 
    lag(month, 1) over (partition by month(v_d) order by v_d) p2,
    lag(year, 1) over (partition by year(v_d) order by v_d) p3,
    first_value(data) over(partition by month(v_d) order by v_d) p4, 
    first_value(month) over(partition by month(v_d) order by v_d) p5,
    first_value(data) over(partition by year(v_d) order by v_d) p6, 
    first_value(year) over(partition by year(v_d) order by v_d) p7 
    from tbl;
        v_d     | data | month | year |     p1     | p2  | p3  | p4  | p5  | p6  | p7  
    ------------+------+-------+------+------------+-----+-----+-----+-----+-----+-----
     2015-01-01 |  100 |   100 |  100 |            |     |     | 100 | 100 | 100 | 100
     2015-01-02 |  200 |   300 |  300 | 2015-01-01 | 100 | 100 | 100 | 100 | 100 | 100
     2015-01-04 |  200 |   500 |  500 | 2015-01-02 | 300 | 300 | 100 | 100 | 100 | 100
    (3 rows)
    解释
    p1  昨天的日期
    p2  当月统计数据范围内,上一条月累加值
    p3  当年统计数据范围内,上一条年累加值
    p4  当月统计数据范围内,第一条月累加值
    p5  当月统计数据范围内,第一条日值
    p6  当年统计数据范围内,第一条年累加值
    p7  当年统计数据范围内,第一条日值
    
    错误判断标准
    1. 缺失日期
    v_d - p1 <>1
    2. 数据错误
    月起始值错误
    p4 <> p5
    年起始值错误
    p6 <> p7
    月累加值错误
    p2+data <> month
    年累加值错误
    p3+data <> year
    
    最终SQL
    pipeline=# select * from 
    (
    select v_d, data, month, year, 
    lag(v_d, 1) over (order by v_d) p1, 
    lag(month, 1) over (partition by month(v_d) order by v_d) p2,
    lag(year, 1) over (partition by year(v_d) order by v_d) p3,
    first_value(data) over(partition by month(v_d) order by v_d) p4, 
    first_value(month) over(partition by month(v_d) order by v_d) p5,
    first_value(data) over(partition by year(v_d) order by v_d) p6, 
    first_value(year) over(partition by year(v_d) order by v_d) p7 
    from tbl)
    t
    where v_d - p1 <>1
    or
    p4 <> p5
    or
    p6 <> p7
    or
    p2+data <> month
    or
    p3+data <> year;
        v_d     | data | month | year |     p1     | p2  | p3  | p4  | p5  | p6  | p7  
    ------------+------+-------+------+------------+-----+-----+-----+-----+-----+-----
     2015-01-04 |  200 |   500 |  500 | 2015-01-02 | 300 | 300 | 100 | 100 | 100 | 100
    (1 row)
    2019-07-17 18:20:40
    赞同 4 展开评论 打赏
问答分类:
SQL
问答地址:
问答排行榜
最热
最新

相关电子书

更多
PolarDB NL2SQL: 帮助您写出准确、优化的SQL 立即下载
Spark SQL 2.0/2.1 Experiences 立即下载
GeoMesa on Spark SQL 立即下载