构建SQL查询：按Product ID统计关联Tickets的序列号总计数

需求说明 #

需要创建一条新的SQL查询，当Tickets表中的Product ID为空时，通过Serial Number字段关联Product表，最终按Product ID统计对应产品的关联总计数。

示例数据 #

Product表 #

|--------------------------------------|
|Product ID | Product Serial NUMBER    |
--------------------------------------
|   1       |    SN-0001               |
----------------------------------------
|   2       |    SN-0002               |
----------------------------------------
|   3       |    SN-0003               |
----------------------------------------
|   4       |    SN-0004               |
----------------------------------------

Tickets表 #

|-------------------------------------------------|
|Ticket ID  |    Product ID  |     Serial Number  |
-------------------------------------------------|
|   1       |        2       |                    |
---------------------------------------------------
|   2       |                |     SN-0003        |
---------------------------------------------------
|   3       |                |     SN-0001        |
--------------------------------------------------
|   4       |        1       |                    |
---------------------------------------------------
|   5       |        1       |                    |
---------------------------------------------------
|   6       |        2       |                    |
---------------------------------------------------

期望查询结果 #

|----------------------------------------------------------------------|
|   ID      |    Product ID              |  Total Serial Number Count  |
-----------------------------------------------------------------------
|   1       |         1                  |        3                    |
------------------------------------------------------------------------
|   2       |         2                  |        2                    |
------------------------------------------------------------------------
|   3       |         3                  |        1                    |
------------------------------------------------------------------------
|   4       |         4                  |        0                    |
------------------------------------------------------------------------

背景原因 #

此前Tickets表存在冗余的序列号字段，现已移除，因此需要通过上述逻辑统计。

当前已编写的查询语句 #

SELECT
     "Tickets"."ID" as "ID",
     COUNT("Tickets"."Product ID") over(PARTITION BY "Tickets"."Product ID"  ) as " Total Machine",
     to_string(count("Tickets"."ID")) as "Total Tickets"
     FROM  "Tickets" 
     GROUP BY "Tickets"."ID",
     "Tickets"."Product ID" 

内容的提问来源于stack exchange，提问作者Kimmy