时间戳

首页 > 解决方案 > 我的查询需要性能改进，有什么建议吗？

sql - 我的查询需要性能改进，有什么建议吗？

问题描述

我正在处理一些问题，我的查询花费的时间比我预期的要长。我正在尝试对我拥有的一些数据进行总结，这些数据来自这两个表：

  1. 支付
CREATE TABLE public.byrdaily (
    datetrans int4 NOT NULL,
    storeid int4 NOT NULL,
    receiptno varchar(12) NOT NULL,
    payment_type varchar(3) NOT NULL,
    bank_code varchar(10) NULL,
    bin_no varchar(15) NULL,
    nilai numeric NULL,
    CONSTRAINT pk_byrdaily PRIMARY KEY (datetrans, storeid, receiptno, payment_type)
);

datetrans|storeid|receiptno   |payment_type|bank_code   |bin_no|nilai
20190820 |0000001|A00001      |Cash        |null        |null  |6000
20190820 |0000001|A00002      |Cash        |null        |null  |6000
20190820 |0000002|A00001      |Cash        |null        |null  |6000
20190820 |0000003|A00001      |Debit       |B01         |15154 |6000
20190820 |0000003|A00002      |Cash        |null        |null  |6000
20190820 |0000003|A00003      |Cash        |null        |null  |6000
20190820 |0000004|A00001      |Debit       |B01         |468788|6000
20190820 |0000004|A00002      |Debit       |B01         |468788|6000
20190820 |0000005|A00001      |Cash        |null        |null  |6000
20190820 |0000005|A00002      |Debit       |null        |874665|6000`
  1. 分支
CREATE TABLE history.store_branch (
    storeid int4 NOT NULL,
    branchid varchar(4) NOT NULL,
    CONSTRAINT pk_store_branch PRIMARY KEY (storeid, branchid)
);

branchid|Storeid
B001    |0000001
B001    |0000002
B001    |0000003
B002    |0000004
B002    |0000005

这是我的查询：

select distinct SUBSTRING(c.datetrans::character varying, 1, 4)::integer as yeartrans,
    SUBSTRING (c.datetrans::character varying, 5,2)::integer as monthtrans,
    s.branchid,
    c.payment_type,
    case when (c.bank_code is NULL) then 'BLANK' else c.bank_code end as bank_code,
    case when (c.bin_no is NULL) then 'BLANK' else c.bin_no end as bin_no,
    sum(c.nilai) as nilai,
    f.frekuensi,
    case when (x.total > 1) then 2 else 1 end as flag
from byrdaily c
inner join history.store_branch s on s.storeid = c.storeid
left join (
    select count(distinct b.payment_type) as total,
        s.branchid,
        b.storeid,
        b.receiptno
    from byrdaily b
    inner join history.store_branch s on s.storeid = b.storeid
    where s.expireddate is null
        and b.payment_type not in ('P01','P02','IST')
        and b.datetrans >= '20190701'
        and b.datetrans < '20190801'
    group by s.branchid, b.storeid, b.receiptno) x
on c.storeid = x.storeid and c.receiptno = x.receiptno
left join (
    select count(b.payment_type) as frekuensi,
        sum(b.nilai) as nilai,
        s.branchid,
        b.storeid,
        b.receiptno
    from byrdaily b
    inner join history.store_branch s on s.storeid = b.storeid 
    where s.expireddate is null
        and b.payment_type not in ('P01','P02','IST')
        and b.datetrans >= '20190701'
        and b.datetrans < '20190801'
    group by s.branchid, b.storeid, b.receiptno) f
on f.storeid = c.storeid and f.receiptno = c.receiptno
where s.expireddate is null
    and c.payment_type not in ('P01','P02','IST')
    and c.datetrans >= '20190701' 
    and c.datetrans < '20190801' 
group by yeartrans, monthtrans, c.payment_type, c.bank_code, c.bin_no, x.total, flag, s.branchid, f.frekuensi

因为有很多聚合和分组，我的查询速度很慢，尤其是现在我需要处理大约 1 亿条数据。

任何建议如何改进它？任何替代解决方案？

标签： sqlpostgresql

解决方案

你会试一试吗？我将分组（每年每月的 nilai 总和）放在一个单独的表中，然后加入其他表

   select t1.ym as year_month,
        s.branchid,
        c.payment_type,
        case when (c.bank_code is NULL) then 'BLANK' else c.bank_code end as bank_code,
        case when (c.bin_no is NULL) then 'BLANK' else c.bin_no end as bin_no,
        t1.sumn as nilai,
        f.frekuensi,
        case when (x.total > 1) then 2 else 1 end as flag
    from (select date_part('year', datetrans)::varchar(4)||date_part('month', datetrans)::varchar(2) as ym, sum(c.nilai) as sumn
            from byrdaily 
            group by date_part('year', datetrans)::varchar(4)||date_part('month', datetrans)::varchar(2)) as t1

    left join byrdaily c on date_part('year', c.datetrans)::varchar(4)||date_part('month', c.datetrans)::varchar(2) = ym

    inner join history.store_branch s on s.storeid = c.storeid
    left join (
        select count(distinct b.payment_type) as total,
            s.branchid,
            b.storeid,
            b.receiptno
        from byrdaily b
        inner join history.store_branch s on s.storeid = b.storeid
        where s.expireddate is null
            and b.payment_type not in ('P01','P02','IST')
            and b.datetrans >= '20190701'
            and b.datetrans < '20190801'
        group by s.branchid, b.storeid, b.receiptno) x
    on c.storeid = x.storeid and c.receiptno = x.receiptno
    left join (
        select count(b.payment_type) as frekuensi,
            sum(b.nilai) as nilai,
            s.branchid,
            b.storeid,
            b.receiptno
        from byrdaily b
        inner join history.store_branch s on s.storeid = b.storeid 
        where s.expireddate is null
            and b.payment_type not in ('P01','P02','IST')
            and b.datetrans >= '20190701'
            and b.datetrans < '20190801'
        group by s.branchid, b.storeid, b.receiptno) f
    on f.storeid = c.storeid and f.receiptno = c.receiptno
    where s.expireddate is null
        and c.payment_type not in ('P01','P02','IST')
        and c.datetrans >= '20190701' 
        and c.datetrans < '20190801'

推荐阅读