时间戳

首页 > 解决方案 > 如何使用 SQL 将百分比添加到我的表中？

sql-server - 如何使用 SQL 将百分比添加到我的表中？

问题描述

我正在尝试计算下面查询中Sign_Ups_with_Want_Created的百分比。Sign_Ups我以为我只能在查询中添加一行代码来划分 sign_ups_with_want_created / sign_ups 但是当我运行查询时出现此错误：

语法错误行 8:1：无法解析列“sign_ups_with_want_created”

代码：

select
    month(From_iso8601_timestamp(u.created)) as Month,
    count(distinct u.id) as Sign_Ups,
    count (distinct w.owner) as Sign_Ups_with_Want_Created,
    count(distinct g.owner) as Sign_Ups_with_Reel_Created,
    count(distinct 
             case 
                when g.status = 'done' then g.owner 
             end) as Sign_Ups_with_Done_Reel,
   count(distinct 
             case 
                when g.status = 'active' then g.owner 
             end) as Sign_Ups_with_Active_Reel
from
    prodjoinreel.users u
left join 
    prodjoinreel.goals g on g.owner = u.id
left join 
    prodjoinreel.wants w on w.owner = u.id
where 
    year(From_iso8601_timestamp(u.created)) = 2019
group by 
    Month
order by 
    Month

谢谢

标签： sql-server

解决方案

我不认为这是基于错误消息的 SQL Server，但尝试完成此操作的一种方法是在百分比公式中再次使用列计算。根据您使用的实际引擎，您可能需要cast()将count()结果转换为十进制以保持除法的精度。

我使用了 T-SQL 语法：

select
  month(From_iso8601_timestamp(u.created)) as Month,
  count(distinct u.id) as Sign_Ups,
  count (distinct w.owner) as Sign_Ups_with_Want_Created,
  count(distinct g.owner) as Sign_Ups_with_Reel_Created,
                count(distinct 
                    case 
                    when g.status = 'done' then g.owner 
                    end
                ) as Sign_Ups_with_Done_Reel,
   count(distinct 
                    case 
                    when g.status = 'active' then g.owner 
                    end
                ) as Sign_Ups_with_Active_Reel,
   /* sign_ups_with_want_created / sign_ups */
   cast(cast(count(distinct w.owner) as decimal(19,4)) / cast(count(distinct u.id) as decimal(19,4)) as decimal(19,4)) as Pct_Sign_Ups_with_Want_Created
from
  prodjoinreel.users u
  left join prodjoinreel.goals g on g.owner = u.id
left join prodjoinreel.wants w on w.owner = u.id
where year(From_iso8601_timestamp(u.created)) = 2019
group by Month
order by Month

以上很简单，但更难维护，本质上是复制代码。在 SQL Server 中，还有其他选项。

另一种解决方案是使用CTE，从临时结果集中计算百分比。

另一种解决方案是将初始结果选择到临时表中；使用临时表得到最终结果；并删除临时表。

推荐阅读