在PostgreSQL中编写此查询的更好方法

Question

我创建了以下查询，基本上将商店合并到特定的组中。这些团体是CDJR，CDJRF，ANP和FARMER。这是我能想到的进行此查询的最有效方法。您知道更快、代码驱动更少的查询吗？我只是在学习如何成为一个更有效的查询编写者，任何输入将不胜感激。请阅读查询下方的更多细节。请注意，为简单起见，我只添加了 4 个商店代码。如果您需要更多信息，请告诉我。

WITH CTE
AS
(
SELECT 
COUNT(D.storecode) AS StoreCount,
D.storeid,
D.storename,                    
D.storecode,                    
D.start,                    
D.organizationid,
CASE
WHEN F.storefranchisetypeid IN (1, 2, 3, 4, 7, 25, 19, 20, 26, 29, 30) THEN 'CDJR'
WHEN F.storefranchisetypeid IN (21, 23, 28) THEN 'FARMER' 
WHEN F.storefranchisetypeid = 22 THEN 'ANP' 
END AS BrandGroup
FROM 
store AS D                                              
JOIN organization AS BO     ON D.organizationid = BO.organizationid
JOIN franchises AS F        ON D.storeid= F.storeid
JOIN franchisetype AS FT    ON F.franchisetypeid = FT.franchisetypeid   
WHERE
BO.countryid = 6            -- USA only.
AND D.end IS NULL           -- Active stores.
AND FT.active IS TRUE       
AND F.end IS NULL           -- Active franchises.
AND D.franchised = 'F'
AND D.trainingtypeid = 1
AND D.storemarket = 'M'
AND D.storecodes IN (45660, 45733, 54816, 60739)
GROUP BY
D.storeid,
D.storename,                    -- Dealer name detailed.
D.storecode,                    -- Dealer code.
D.start,
D.organizationid,
CASE
WHEN F.storefranchisetypeid IN (1, 2, 3, 4, 7, 25, 19, 20, 26, 29, 30) THEN 'CDJR'
WHEN F.storefranchisetypeid IN (21, 23, 28) THEN 'FARMER' 
WHEN F.storefranchisetypeid = 22 THEN 'ANP' 
END
),
CTE2
AS
(
SELECT *
FROM
CTE
EXCEPT
SELECT *
FROM
CTE
WHERE
storecount <= 4
AND brandgroup = 'CDJR'
),
CTE3
AS
(
SELECT
'CDJRF' AS FranchiseGroup,
storeid,
storename,
storecode,
start,
organizationid
FROM
CTE2
GROUP BY
storeid,
storename,
storecode,
start,
organizationid
HAVING
COUNT(storecode) >= 2
UNION
SELECT
BrandGroup,
storeid,
storename,
storecode,
start,
organizationid
FROM
CTE2
WHERE
storecode NOT IN
(
SELECT
storecode
FROM
CTE2
GROUP BY
storecode
HAVING
COUNT(storecode) >= 2
)
)
SELECT *
FROM CTE3

结果：
特许经营集团;存储ID;商店名称;商店代码;开始;组织标识

1. "CDJR";520;"帕克市场";"05002";"1954-11-24";1336

   年

2. "农民";763;"乔·墨菲的农场";"23355";"1980-04-24";1579

   年

3. "ANP";80549;"A&P";69069";"2011-04-25";283224

4. "CDJRF";623;"克拉格斯";09733";"1952-01-24";1439

   年

在下面的示例的第一个 CTE 中，我提取存储数据，对其进行计数，并将类型 ID 最初分为 3 组，而不是 4 组。CTE 的结果在 CTE 查询下方提供。

SELECT 
COUNT(D.storecode) AS StoreCount,
D.storeid,
D.storename,                    
D.storecode,                    
D.start,                    
D.organizationid,
CASE
WHEN F.storefranchisetypeid IN (1, 2, 3, 4, 7, 25, 19, 20, 26, 29, 30) THEN 'CDJR'
WHEN F.storefranchisetypeid IN (21, 23, 28) THEN 'FARMER' 
WHEN F.storefranchisetypeid = 22 THEN 'ANP' 
END AS BrandGroup
FROM 
store AS D                                              
JOIN organization AS BO     ON D.organizationid = BO.organizationid
JOIN franchises AS F        ON D.storeid= F.storeid
JOIN franchisetype AS FT    ON F.franchisetypeid = FT.franchisetypeid   
WHERE
BO.countryid = 6            -- USA only.
AND D.end IS NULL           -- Active stores.
AND FT.active IS TRUE       
AND F.end IS NULL           -- Active franchises.
AND D.franchised = 'F'
AND D.trainingtypeid = 1
AND D.storemarket = 'M'
AND D.storecodes IN (45660, 45733, 54816, 60739)
GROUP BY
D.storeid,
D.storename,                    -- Dealer name detailed.
D.storecode,                    -- Dealer code.
D.start,
D.organizationid,
CASE
WHEN F.storefranchisetypeid IN (1, 2, 3, 4, 7, 25, 19, 20, 26, 29, 30) THEN 'CDJR'
WHEN F.storefranchisetypeid IN (21, 23, 28) THEN 'FARMER' 
WHEN F.storefranchisetypeid = 22 THEN 'ANP' 
END

结果：
存储计数;存储ID;商店名称;商店代码;开始;组织标识;品牌集团

1. 1;80549;"A&P";69069";"2011-04-25";283224;"安普">

2. 阿拉伯数字;763;"克拉格斯";23355";"1980-04-24";1579;"CDJR">

3. 1;763;"克拉格斯";23355";"1980-04-24";1579;"农民">

4. 7;623;"乔·墨菲的农场";"09733";"1952-01-24";1439;"CDJR">

5. 1;623;"乔·墨菲的农场";"09733";"1952-01-24";1439;"农民">

6. 6;520;"帕克农场";"05002";"1954-11-24";1336;"CDJR">

第二个 CTE，CTE2，删除了不相关的 CDRJ。结果位于查询下方。

CTE2
AS
(
SELECT *
FROM
CTE
EXCEPT
SELECT *
FROM
CTE
WHERE
storecount <= 4
AND brandgroup = 'CDJR'

结果：
存储计数;存储ID;商店名称;商店代码;开始;组织标识;品牌集团

1. 6;520;"帕克农场";"05002";"1954-11-24";1336;"CDJR">

2. 1;80549;"A&P";69069";"2011-04- 25";283224;"安普">

3. 1;623;"乔·墨菲的农场";"09733";"1952-01-24";1439;"菲亚特">

4. 7;623;"乔·墨菲的农场";"09733";"1952-01-24";1439;"CDJR">

5. 1;763;"克拉格斯";23355";"1980-04-24";1579;"菲亚特">

最后一个 CTE，CTE3，在我计算了有多少组之后，将 CDRJF 结果与其他组结合起来。结果都指向页面顶部的第一组结果。

SELECT
'CDJRF' AS FranchiseGroup,
storeid,
storename,
storecode,
start,
organizationid
FROM
CTE2
GROUP BY
storeid,
storename,
storecode,
start,
organizationid
HAVING
COUNT(storecode) >= 2
UNION
SELECT
BrandGroup,
storeid,
storename,
storecode,
start,
organizationid
FROM
CTE2
WHERE
storecode NOT IN
(
SELECT
storecode
FROM
CTE2
GROUP BY
storecode
HAVING
COUNT(storecode) >= 2
)

Answer 1

好

1. 查询格式良好且可读性强。

2. 您意识到它的效率并不高。

坏的

1. 示例数据的格式不正确且易于使用。

主要问题只是简化逻辑。这是未经测试的(请参阅错误点#1(，但它可能会让您了解如何清理它。

SELECT DISTINCT *
FROM ( SELECT CASE WHEN COUNT(storeCode) OVER 
( PARTITION BY  storeid,
storename,
storecode,
start,
organizationid
) >= 2 THEN 'CDJRF'
ELSE BandGroup
END,
storeid,
storename,
storecode,
start,
organizationid
FROM (
SELECT 
COUNT(D.storecode) AS StoreCount,
D.storeid,
D.storename,                    
D.storecode,                    
D.start,                    
D.organizationid,
F.BrandGroup
FROM 
store AS D                                              
JOIN organization AS BO     ON D.organizationid = BO.organizationid
JOIN (SELECT store_id,
CASE
WHEN F.storefranchisetypeid IN (1, 2, 3, 4, 7, 25, 19, 20, 26, 29, 30) THEN 'CDJR'
WHEN F.storefranchisetypeid IN (21, 23, 28) THEN 'FARMER' 
WHEN F.storefranchisetypeid = 22 THEN 'ANP' 
END AS BrandGroup,
end
FROM franchises
) F
ON D.storeid= F.storeid
JOIN franchisetype AS FT    ON F.franchisetypeid = FT.franchisetypeid   
WHERE
BO.countryid = 6            -- USA only.
AND D.end IS NULL           -- Active stores.
AND FT.active IS TRUE       
AND F.end IS NULL           -- Active franchises.
AND D.franchised = 'F'
AND D.trainingtypeid = 1
AND D.storemarket = 'M'
AND D.storecodes IN (45660, 45733, 54816, 60739)
GROUP BY
D.storeid,
D.storename,                    -- Dealer name detailed.
D.storecode,                    -- Dealer code.
D.start,
D.organizationid,
F.BrandGroup
) CTE
WHERE storeCount > 4
OR bandGroup <> 'CDJR'
) SUB;