Oracle如何在查询返回矩阵表时优化计数案例

vbkedwbf 于 2023-03-07 发布在 Oracle

关注(0)|答案(2)|浏览(134)

我有一个表显示的人的生日，他们是在一个类别（生日波1列：VIP、忠诚、常规、一次性、过期、不活动、离开、无RFM），以及他们在下一个生日时移动到的地方，称为“生日波2”。
我创建了case语句来计算类别移动，但它相当长。

select DISTINCT Birthday_Wave_1
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS a
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS b
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS c
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS d
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS e
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS f
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS g
,COUNT(case when Birthday_Wave_1 = 'VIP' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS h

,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) AS i
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS j
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS k
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS l
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS m
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS n
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS o
,COUNT(case when Birthday_Wave_1 = 'LOYAL' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS p

,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as q
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS r
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS s
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS t
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS u
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS v
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS w
,COUNT(case when Birthday_Wave_1 = 'REGULAR' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS x

,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as y
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS z
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS aa
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS ab
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS ac
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS ad
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS ae
,COUNT(case when Birthday_Wave_1 = 'ONE-OFFS' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS af

,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as ag
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS ah
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS ai
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS aj
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS ak
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS al
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS am
,COUNT(case when Birthday_Wave_1 = 'NEW' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS an

,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as ao
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS apa
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS aq
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS ar
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS art
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS at
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS au
,COUNT(case when Birthday_Wave_1 = 'LAPSED' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS av

,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as aw
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS ax
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS ay
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS az
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS ba
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS bb
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS bc
,COUNT(case when Birthday_Wave_1 = 'INACTIVE' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS bd

,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as be
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS be
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS bf
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS bg
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS bh
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS bi
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS bj
,COUNT(case when Birthday_Wave_1 = 'GONE AWAY' and Birthday_Wave_2 = 'NO RFM' THEN CONTACT_KEY ELSE NULL END) AS bk

,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'VIP' THEN CONTACT_KEY ELSE NULL END) as bl
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'LOYAL' THEN CONTACT_KEY ELSE NULL END) AS bm
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'REGULAR' THEN CONTACT_KEY ELSE NULL END) AS bn
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'ONE-OFFS' THEN CONTACT_KEY ELSE NULL END) AS bo
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'NEW' THEN CONTACT_KEY ELSE NULL END) AS bp
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'LAPSED' THEN CONTACT_KEY ELSE NULL END) AS bq
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'INACTIVE' THEN CONTACT_KEY ELSE NULL END) AS br
,COUNT(case when Birthday_Wave_1 = 'NO RFM' and Birthday_Wave_2 = 'GONE AWAY' THEN CONTACT_KEY ELSE NULL END) AS bs

from rfm_bd
GROUP BY Birthday_Wave_1;

下面是输出的一部分，然后我手动删除了多余的数据，在excel中创建了类别矩阵。SQL Output
这是最终产品。在SQL Oracle中是否有优化的方法来创建此矩阵？Excel Matrix

oracle

来源：https://stackoverflow.com/questions/75520038/oracle-how-to-optimise-a-count-case-when-query-to-return-a-matrix-table

2条答案

按热度按时间

gtlvzcf81#

首先，在select之后立即使用distinct是没有意义的，使用GROUP BY Birthday_Wave_1可以确保没有重复项。
然后，我会尝试以下几种方法：

With tb_grp1 as (
  select birthday_wave_1, birthday_wave_2, count(*) cnt
  from rfm_bd
  GROUP BY Birthday_Wave_1
)
select Birthday_Wave_1
  ,sum(case when Birthday_Wave_1 = 'VIP' 
     and Birthday_Wave_2 = 'LOYAL' 
      THEN cnt ELSE 0 END) AS a
  --- the other columns
from tb_grp1
GROUP BY Birthday_Wave_1;

不确定以上治疗会有多大帮助，但那种截然不同的肯定是没有好处的。

赞(0）回复(0）举报 2023-03-07

juzqafwq2#

也许您应该研究一下PIVOT以获得您的“矩阵”：

-- Sample data
WITH
    tbl (ID, BDAY_1, BDAY_2, CONTACT) AS
        (   Select 1,  'VIP',       'LOYAL',        101 From Dual   Union All
            Select 2,  'LOYAL',     'REGULAR',      102 From Dual   Union All
            Select 3,  'REGULAR',   'VIP',          103 From Dual   Union All
            Select 4,  'ONE-OFFS',  'LAPSED',       104 From Dual   Union All
            Select 5,  'LAPSED',    'INACTIVE',     105 From Dual   Union All
            Select 6,  'INACTIVE',  'ONE-OFFS',     106 From Dual   Union All
            Select 7,  'GONE AWAY', 'NO RFM',       107 From Dual   Union All
            Select 8,  'NO RFM',    'GONE AWAY',    108 From Dual   Union All
            Select 9,  'REGULAR',   'VIP',          109 From Dual   Union All
            Select 10, 'LAPSED',    'LOYAL',        110 From Dual   Union All
            Select 11, 'NO RFM',    'VIP',          111 From Dual   Union All
            Select 12, 'VIP',       'INACTIVE',     112 From Dual   Union All
            Select 13, 'VIP',       'LOYAL',        113 From Dual   Union All
            Select 14, 'VIP',       'GONE AWAY',    114 From Dual
        )
--  SQL
SELECT BDAY_1, Sum(A) "VIP", Sum(B) "LOYAL", Sum(C) "REGULAR", Sum(D) "ONE_OFFS", Sum(E) "LAPSED", Sum(F) "INACTIVE", Sum(G) "GONE_AWAY", Sum(H) "NO_RFM"
FROM (   Select ID, BDAY_1, BDAY_2, CONTACT From tbl )
    PIVOT   (Count(ID) For BDAY_2 IN('VIP' "A", 'LOYAL' "B", 'REGULAR' "C", 'ONE-OFFS' "D", 'LAPSED' "E", 'INACTIVE' "F", 'GONE AWAY' "G", 'NO RFM' "H")
            )
GROUP BY BDAY_1

BDAY_1           VIP      LOYAL    REGULAR   ONE_OFFS     LAPSED   INACTIVE  GONE_AWAY     NO_RFM
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
NO RFM             1          0          0          0          0          0          1          0 
VIP                0          2          0          0          0          1          1          0 
GONE AWAY          0          0          0          0          0          0          0          1 
ONE-OFFS           0          0          0          0          1          0          0          0 
LAPSED             0          1          0          0          0          1          0          0 
INACTIVE           0          0          0          1          0          0          0          0 
LOYAL              0          0          1          0          0          0          0          0 
REGULAR            2          0          0          0          0          0          0          0

注意：这只是一个基于样本数据的选项--你应该根据你的数据表/列和数据库上下文来调整它。

赞(0）回复(0）举报 2023-03-07

我来回答

Oracle如何在查询返回矩阵表时优化计数案例

2条答案

相关问题

热门标签

最新问答