sql - PostgreSQL 连接子查询

Question

我有以下表格:

svc_sms:

sms_pk | sms_title

和

svc_sms_msg:

msg_pk | sms_text | msg_status | sms_pk (FK to svc_sms.sms_pk)

我想通过 msg_status 查询和分组 svc_sms_msg 中的所有行，所以我这样做:

    SELECT sms.sms_pk, msg.msg_status, COUNT(msg.msg_status) as num
        FROM svc_sms_msg as msg
        INNER JOIN svc_sms as sms ON sms.sms_pk = msg.sms_pk
        GROUP BY sms.sms_pk, msg.msg_status
        ORDER BY sms.sms_pk, msg.msg_status

现在假设 msg.msg_status 的范围仅在 0 到 4 之间，因此我需要获取每个带有 sms_status = 'x' 的短信出现在 sms_pk 分组中的次数。我需要我的结果是这样的:

> sms.sms_pk  |  msg.msg_status  |  num 
> 
> 1                   0               1
> 
> 1                   1               5
> 
> 1                   2               4
> 
> 1                   3               20
> 
> 1                   4               18
> 
> 2                   0                5
> 
> 2                   1                0
> 
> 2                   2                3
> 
> 2                   3                23
> 
> 2                   4                0

但是当没有行(在 msg 中)带有'msg_status = x'时，连接没有给我任何东西，我需要显示“statuses”的数量，即使它们是 0's在我这样做之前:

(SELECT SUM(CASE WHEN (msg_status = 0) THEN 1 ELSE 0 END) FROM svc_sms_msg WHERE sms_pk = svc_sms.sms_pk) as cnt_initial");
(SELECT SUM(CASE WHEN (msg_status = 1) THEN 1 ELSE 0 END) FROM svc_sms_msg WHERE sms_pk = svc_sms.sms_pk) as cnt_pending");
(SELECT SUM(CASE WHEN (msg_status = 2) THEN 1 ELSE 0 END) FROM svc_sms_msg WHERE sms_pk = svc_sms.sms_pk) as cnt_sended");

等等....但在我的案例中证明它太慢且不切实际。我怎样才能更有效地做到这一点？谢谢

Answer 1

您可以创建所有状态(0 到 4)的派生表(下面的 All_Status)并将其交叉连接到您的 svc_sms，然后左连接到 svc_sms_msg

交叉连接确保每个 SVC_SMS 都具有所有状态，即使不存在消息也是如此，因此此类记录的计数将为零而不是丢失。通过在 sms_pk 和“状态”上左连接到 svc_sms_msg，我们保留所有交叉连接的记录，确保每个状态都分配给每个 SMS_PK；因此，当 msg.msg_status 不存在(为空)时，计数将为 0

SELECT sms.sms_pk, msg.msg_status, COUNT(msg.msg_status) as num
FROM svc_sms as sms 
CROSS JOIN (SELECT unnest(array[0,1,2,3,4]) status) All_Status
LEFT JOIN svc_sms_msg as msg
  ON sms.sms_pk = msg.sms_pk
 AND All_Status.Status = msg.msg_Status
GROUP BY sms.sms_pk, msg.msg_status
ORDER BY sms.sms_pk, msg.msg_status

这取决于您是希望每个状态有更多列还是每个状态和 sms_pk 有一行。

给定此方法，总行数应等于 5 * # of SMS_Pk。