sql - 每小时对数据进行分组并插入到 Postgres 中的汇总表中

Question

我有一个包含大约 50 个表的数据库(目前)，由于倒入几个表中的数据量很大，我的任务是创建一个每小时的数据汇总并将其转储到另一个表中。因此，在原始数据上运行报告需要很长时间，因为新数据库(2 周前)在我为其提取数据的两个表之一中已经有多达 20 万条记录。

查询得到三个可能的客户结果——“cust1”、“cust2”和“cust3”，每个结果都有一个选择的卡片(产品质量调查问卷的邮件回复和潜在获奖)，这是 13 个选择之一。字母表示(“A”Ace、“K”King 等相关值)

这是其中一个子查询和相应的结果:

select sp_cust_card_sequence(cards.cust1) as seq, cards.cust1 as card, count(cards.cust1) as card_count, rtt.game_id as game_id, gt.promo_id as promo_id, gt.choice_id as choice_id, extract(hour from header.start_timestamp) as hour, header.start_timestamp::timestamp::date as date
    from game_bac_cards cards
    inner join card_cust_resp header ON (header.id = cards.game_id)
    inner join game_table gt ON (header.promo_id = gt.promo_id)
    inner join ref_table_type rtt ON (gt.table_type_id = rtt.id)
    where result <> 'undef' 
    group by date, hour, card, rtt.game_id, gt.promo_id, gt.choice_id, cards.cust1

基本上，例如，我想按小时对卡片列中“K”的所有值进行分组。通过下面的查询，我似乎能够几乎实现这一目标，但下面的代码片段显示了“K”小时“19 12/4/2014”上的“strong>”有两个条目，而不是只有一个条目。我相信有一种更优雅的方法可以做到这一点。

最终查询:

select date, hour, card, seq, sum(card_count) as card_count, game_id, choice_id, promo_id 
from (
select date, hour, card, seq, sum(card_count) as card_count, game_id, choice_id, promo_id from (
    select sp_cust_card_sequence(cards.cust1) as seq, cards.cust1 as card, count(cards.cust1) as card_count, rtt.game_id as game_id, gt.promo_id as promo_id, gt.choice_id as choice_id, extract(hour from header.start_timestamp) as hour, header.start_timestamp::timestamp::date as date
    from game_bac_cards cards
    inner join card_cust_resp header ON (header.id = cards.game_id)
    inner join game_table gt ON (header.promo_id = gt.promo_id)
    inner join ref_table_type rtt ON (gt.table_type_id = rtt.id)
    where result <> 'undef' 
    group by date, hour, card, rtt.game_id, gt.promo_id, gt.choice_id, cards.cust1
) as cust1_table
where cust1_table.card is not null and cust1_table.card <> ''
group by date, hour, card_count, card, seq, game_id, choice_id, promo_id

union all

select date, hour, card, seq, sum(card_count) as card_count, game_id, choice_id, promo_id from (
    select sp_cust_card_sequence(cards.cust2) as seq, cards.cust2 as card, count(cards.cust2) as card_count, rtt.game_id as game_id, gt.promo_id as promo_id, gt.choice_id as choice_id, extract(hour from header.start_timestamp) as hour, header.start_timestamp::timestamp::date as date
    from game_bac_cards cards
    inner join card_cust_resp header ON (header.id = cards.game_id)
    inner join game_table gt ON (header.promo_id = gt.promo_id)
    inner join ref_table_type rtt ON (gt.table_type_id = rtt.id)
    where result <> 'undef' 
    group by date, hour, card, rtt.game_id, gt.promo_id, gt.choice_id, cards.cust2
) as cust2_table
where cust2_table.card is not null and cust2_table.card <> ''
group by date, hour, card_count, card, seq, game_id, choice_id, promo_id

union all

select date, hour, card, seq, sum(card_count) as card_count, game_id, choice_id, promo_id from (
    select sp_cust_card_sequence(cards.cust3) as seq, cards.cust3 as card, count(cards.cust3) as card_count, rtt.game_id as game_id, gt.promo_id as promo_id, gt.choice_id as choice_id, extract(hour from header.start_timestamp) as hour, header.start_timestamp::timestamp::date as date
    from game_bac_cards cards
    inner join card_cust_resp header ON (header.id = cards.game_id)
    inner join game_table gt ON (header.promo_id = gt.promo_id)
    inner join ref_table_type rtt ON (gt.table_type_id = rtt.id)
    where result <> 'undef' 
    group by date, hour, card, rtt.game_id, gt.promo_id, gt.choice_id, cards.cust3
) as cust3_table
where cust3_table.card is not null and cust3_table.card <> ''
group by date, hour, card_count, card, seq, game_id, choice_id, promo_id
) as card_details
and card_details.card is not null and card_details.card <> ''
group by date, hour, card, card_count, seq, game_id, choice_id, promo_id
order by date, hour, seq

结果只显示卡片 K |小时 19 |日期 12/4/2014。这应该只有一行而不是两行。 (?)

非常感谢任何帮助!

Answer 1

尝试删除外部查询中 card_count 的分组

按日期、小时、card_count、card、seq、game_id、choice_id、promo_id 分组