postgresql 是什么导致查询产生错误的百分比变化结果？[关闭]

pod7payv 于 2023-11-18 发布在 PostgreSQL

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

已关闭。此问题需要details or clarity。目前不接受回答。
**要改进此问题吗？**通过editing this post添加详细信息并阐明问题。

9天前关闭
Improve this question的
背景资料：from_current是一年（-2表示2年前，-1表示1年前）。下面的查询假设显示按2年前和1年前分组的每个类别中购买的物品。第3行假设显示2年前和1年前之间的变化。而不是显示结果的真实的百分比变化，查询结果显示为0.00，这是什么原因造成的？
以下是我的查询：

WITH YearlyCounts AS (
    SELECT
        from_current,
        COUNT(CASE WHEN accessories > 0 THEN 1 ELSE NULL END) as accessories,
        COUNT(CASE WHEN camping > 0 THEN 1 ELSE NULL END) as camping,
        COUNT(CASE WHEN footwear > 0 THEN 1 ELSE NULL END) as footwear,
        COUNT(CASE WHEN apparel > 0 THEN 1 ELSE NULL END) as apparel,
        COUNT(CASE WHEN kids_clothes > 0 THEN 1 ELSE NULL END) as kids_clothes,
        COUNT(CASE WHEN biking > 0 THEN 1 ELSE NULL END) as biking,
        COUNT(CASE WHEN ski_snow_gear > 0 THEN 1 ELSE NULL END) as ski_snow_gear,
        COUNT(CASE WHEN fishing > 0 THEN 1 ELSE NULL END) as fishing
    FROM tenpeaks_sales
    WHERE loyalty = 't'
    GROUP BY from_current
)
SELECT 
from_current,
accessories,
camping,
footwear,
apparel,
kids_clothes,
biking,
ski_snow_gear,
fishing
FROM YearlyCounts

UNION ALL

SELECT
-1 AS from_current,
    ROUND(((S1.accessories - S2.accessories)/S2.accessories)*100, 2) AS access_change_1_year_ago,
      ROUND(((S1.camping - S2.camping)/S2.camping)*100, 2) AS camping_change_1_year_ago,
      ROUND(((S1.footwear - S2.footwear)/S2.footwear)*100, 2) AS footwear_change_1_year_ago,
      ROUND(((S1.apparel - S2.apparel)/S2.apparel)*100, 2) AS apparel_change_1_year_ago,
      ROUND(((S1.kids_clothes - S2.kids_clothes)/S2.kids_clothes)*100, 2) AS kids_clothes_change_1_year_ago,
      ROUND(((S1.biking - S2.biking)/S2.biking)*100, 2) AS biking_change_1_year_ago,
      ROUND(((S1.ski_snow_gear - S2.ski_snow_gear)/S2.ski_snow_gear)*100, 2) AS ski_snow_gear_change_1_year_ago,
      ROUND(((S1.fishing - S2.fishing)/S2.fishing)*100, 2) AS fishing_change_1_year_ago
FROM YearlyCounts S1
LEFT JOIN YearlyCounts S2 ON S1.from_current = S2.from_current + 1
WHERE S1.from_current = -1
ORDER BY from_current ASC;

字符串
这是我查询的结果：x1c 0d1x
我想第3行实际上显示计算的百分比变化为每个类别，但相反，它是显示0.00.请帮助.谢谢你这么多提前.

postgresql

来源：https://stackoverflow.com/questions/77441910/what-is-causing-the-query-to-produce-the-wrong-percentage-change-result

2条答案

按热度按时间

bvjveswy1#

bigint（*）返回一个bigint，一个bigint除以另一个bigint返回一个bigint。这永远不会是小数，所以没有小数。将数据转换为numeric就可以了：

WITH
    yearlycounts AS (SELECT from_current
                          , COUNT(CASE WHEN accessories > 0 THEN 1 ELSE NULL END)   AS accessories
                          , COUNT(CASE WHEN camping > 0 THEN 1 ELSE NULL END)       AS camping
                          , COUNT(CASE WHEN footwear > 0 THEN 1 ELSE NULL END)      AS footwear
                          , COUNT(CASE WHEN apparel > 0 THEN 1 ELSE NULL END)       AS apparel
                          , COUNT(CASE WHEN kids_clothes > 0 THEN 1 ELSE NULL END)  AS kids_clothes
                          , COUNT(CASE WHEN biking > 0 THEN 1 ELSE NULL END)        AS biking
                          , COUNT(CASE WHEN ski_snow_gear > 0 THEN 1 ELSE NULL END) AS ski_snow_gear
                          , COUNT(CASE WHEN fishing > 0 THEN 1 ELSE NULL END)       AS fishing
                     FROM tenpeaks_sales
                     WHERE loyalty = 't'
                     GROUP BY from_current)
SELECT from_current
     , accessories
     , camping
     , footwear
     , apparel
     , kids_clothes
     , biking
     , ski_snow_gear
     , fishing
FROM yearlycounts
UNION ALL
SELECT -1                                                                         AS from_current
     , ROUND(((s1.accessories - s2.accessories) / s2.accessories::numeric) * 100, 2)       AS access_change_1_year_ago
     , ROUND(((s1.camping - s2.camping) / s2.camping::numeric) * 100, 2)                   AS camping_change_1_year_ago
     , ROUND(((s1.footwear - s2.footwear) / s2.footwear::numeric) * 100, 2)                AS footwear_change_1_year_ago
     , ROUND(((s1.apparel - s2.apparel) / s2.apparel::numeric) * 100, 2)                   AS apparel_change_1_year_ago
     , ROUND(((s1.kids_clothes - s2.kids_clothes::numeric) / s2.kids_clothes) * 100, 2)    AS kids_clothes_change_1_year_ago
     , ROUND(((s1.biking - s2.biking) / s2.biking::numeric) * 100, 2)                      AS biking_change_1_year_ago
     , ROUND(((s1.ski_snow_gear - s2.ski_snow_gear::numeric) / s2.ski_snow_gear) * 100, 2) AS ski_snow_gear_change_1_year_ago
     , ROUND(((s1.fishing - s2.fishing) / s2.fishing::numeric) * 100, 2)                   AS fishing_change_1_year_ago
FROM yearlycounts               s1
         LEFT JOIN yearlycounts s2 ON s1.from_current = s2.from_current + 1
WHERE s1.from_current = -1
ORDER BY from_current ASC;

字符串

赞(0）回复(0）举报 2023-11-18

j2datikz2#

太多的代码会让阅读感到麻烦（以后，在发布之前，请缩小问题的范围），但问题几乎肯定是整数除法。
当你将两个整数相除时，结果也是一个带有任何小数部分截断的整数，这意味着使用整数操作数的百分比计算往往最终显示为0，除非你努力将一边转换为浮点类型（将任一操作数乘以1.0通常会这样做）。

赞(0）回复(0）举报 2023-11-18