V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Distributions
Ubuntu
Fedora
CentOS
中文资源站
网易开源镜像站
zhangolve
V2EX  ›  Linux

请 sql 老司机来解答解答

  •  
  •   zhangolve · 2018-12-20 10:25:56 +08:00 · 2228 次点击
    这是一个创建于 1925 天前的主题,其中的信息可能已经有所发展或是发生改变。

    https://segmentfault.com/ 上问了个问题,结果并没有人理。

    如何用 postgresql 语句实现数据的累加

    12 条回复    2018-12-21 16:41:26 +08:00
    cyhulk
        1
    cyhulk  
       2018-12-20 10:58:01 +08:00
    mysql 可以增加局部变量,select 局部变量做操作就行了
    wqzjk393
        2
    wqzjk393  
       2018-12-20 10:58:02 +08:00
    对第二张表 group by id,然后 sum 一下总的消费金额 生成一个临时表 3。剩余金额就是 select (表 1.总金额-表 3.消费金额) from 表 1 join 表 3。不知道是不是这个意思
    CRVV
        3
    CRVV  
       2018-12-20 11:35:52 +08:00   ❤️ 1
    WITH data AS (
    SELECT created_at, id AS card_id, total, total AS amount FROM card
    UNION ALL
    SELECT transaction.created_at, card_id, card.total, -transaction.total FROM transaction INNER JOIN card ON card_id = card.id)
    SELECT created_at, card_id, total, sum(amount) OVER (PARTITION BY card_id ORDER BY created_at) FROM data;

    重点是 window function,sum(amount) OVER (PARTITION BY card_id ORDER BY created_at)
    zhangolve
        4
    zhangolve  
    OP
       2018-12-20 12:41:53 +08:00
    @CRVV 我要看看,先谢过。
    zhangolve
        5
    zhangolve  
    OP
       2018-12-20 13:47:56 +08:00
    @CRVV 试过了,可行。再次感谢,之前对 window function 没太多了解。
    suiterchik
        6
    suiterchik  
       2018-12-20 13:51:56 +08:00
    都上窗函数了为啥不
    sum(amount) over(
    partition by card_id
    order by created_at
    rows between unbounded preceding
    and current row
    )
    这个可以计算截至到当前行的支付总额
    ziding
        7
    ziding  
       2018-12-20 14:02:42 +08:00
    这种需求建议在业务层处理,窗口函数用起来爽,等到后面加需求的时候,慢慢慢慢的性能就被脱下来了。
    suiterchik
        8
    suiterchik  
       2018-12-20 14:03:40 +08:00
    @CRVV 你这个 sql 不约束行号不行吧,OVER (PARTITION BY card_id ORDER BY created_at) 这个窗,card_id 相同的记录返回的值都是一样的
    CRVV
        9
    CRVV  
       2018-12-20 14:58:16 +08:00
    @suiterchik
    楼主都回帖说能用了,那当然是能用的
    你加的那个 ROWS BETWEEN ... 是默认值,加不加都一样
    suiterchik
        10
    suiterchik  
       2018-12-20 17:44:34 +08:00 via Android
    @CRVV 默认的 rows between 是 rows between unbounded preceding and unbounded following,楼主的需求是求当前时间的累积,显然要约束在 current row
    zhangolve
        11
    zhangolve  
    OP
       2018-12-21 15:43:35 +08:00
    @CRVV 我说的能用是,理解了这个意思,经过我自己改造后能用。哈哈。
    zhangolve
        12
    zhangolve  
    OP
       2018-12-21 16:41:26 +08:00
    WITH data AS (
    SELECT created_at, id AS card_id, total, total AS remaining_total FROM prepay_card
    UNION ALL
    SELECT pct.created_at, pct.prepay_card_id AS card_id, pc.total, -pct.total AS remaining_total
    FROM prepay_card_transaction pct
    INNER JOIN prepay_card pc ON pct.prepay_card_id = pc.id
    )
    SELECT created_at, card_id, total,sum(remaining_total) OVER (PARTITION BY card_id ORDER BY created_at) AS remaining_total_all FROM data
    ORDER BY card_id, created_at ;

    补充一下我根据 @CRVV 改写的 sql
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   942 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 20:56 · PVG 04:56 · LAX 13:56 · JFK 16:56
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.