如何简化包含计算的 mssql 查询

Question

>我有 3 个表，其中包含我试图获取计数然后进行计算的数据。我有一个有效的查询，但它重复。

SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as 'Num_To_Go',
(SELECT COUNT(*) from place_been where people.person_id=person_id) as 'Num_Visited',
((​SELECT​ COUNT(*)​ ​FROM​ place_been ​WHERE​ people.person_id=person_id)​ /​ (​SELECT COUNT(*) ​FROM​ place_to_go ​WHERE​ people.person_id=person_id))​ ​*​ 100 ​AS​ ​'Perc_Visited'
FROM people;

我正在尝试完成的是不要对百分比计算进行重复的子查询。我为此所做的任何更改都会在语法错误中结束，这变得非常令人沮丧。

以为我可能已经能够使用

SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as 'Num_To_Go',
(SELECT COUNT(*) from place_been where people.person_id=person_id) as 'Num_Visited',
(CONVERT(DECIMAL(3,0), 'Num_To_Go'))/(CONVERT(DECIMAL(3,0), 'Num_Visited')​) ​*​ 100 ​AS​ ​'Perc_Visited'
FROM people;

但这最终导致将数据类型 varchar 转换为数字时出错

任何指示将不胜感激。

Answer 1

我会使用APPLY：

SELECT person_id, Num_To_Go, Num_Visited, (Num_To_Go * 1.0 / Num_Visited) * 100 AS Perc_Visited
FROM people p OUTER APPLY
( SELECT COUNT(*) AS Num_To_Go
FROM place_to_go pg
WHERE P.person_id = pg.person_id
) pg OUTER APPLY
( SELECT COUNT(*) AS Num_Visited
FROM place_been pb
WHERE p.person_id = pb.person_id
) pb;

Answer 2

您可以尝试使用子查询

select *, (CONVERT(DECIMAL(3,0), Num_To_Go))/(CONVERT(DECIMAL(3,0), Num_Visited)​) ​*​ 100.00 ​AS​ ​'Perc_Visited'
from
(
SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as 'Num_To_Go',
(SELECT COUNT(*) from place_been where people.person_id=person_id) as 'Num_Visited',
FROM people
)A

Answer 3

然而，这也许

有点黑暗：

SELECT p.person_id,
COUNT(DISTINCT p2g.{id_column}) AS NumToGo,
COUNT(DISTINCT pb.{id_column}) AS NNumVisited,
((COUNT(DISTINCT pb.{id_column}) * 1.0) / COUNT(DISTINCT p2g.{id_column})) * 100 AS Perc_Visited --* 1.0 due to integer math. I.e. 99/100 = 0
FROM people p
LEFT JOIN place_to_go p2g ON p.person_id = p2g.person_id
LEFT JOIN place_been pb ON p.person_id = pb.person_id
GROUP BY person_id;

Answer 4

以下是我如何解决它： 可运行的示例

select ppl.person_id
, coalesce(ptg.cnt,0) as 'Num_To_Go'
, coalesce(pb.cnt,0) as 'Num_Visited'
, case 
when coalesce(ptg.cnt,0) = 0 then 100 --avoid /0 error ; if there are no places to go let's say we've been to them all
else 100.0 * coalesce(pb.cnt,0) / ptg.cnt
end 'Perc_Visited'
from people ppl
left outer join (select person_id, count(1) cnt from place_to_go group by person_id) ptg on ptg.person_id = ppl.person_id
left outer join (select person_id, count(1) cnt from place_been group by person_id) pb on pb.person_id = ppl.person_id

• 我已经移动了查询以将计数放入 FROM 子句下的子查询中;因此，您可以为每个表(place_to_go、place_been(获得每人一次计数，然后在需要时随时重用这些结果。
• 我使用 person_id 字段连接这些子查询。 我使用了left outer joins，这样即使一个人在任一表中都没有任何记录，我们仍然会在结果中看到这个人。
• 我使用coalesce(cnt,0)来确保没有与0我们看到的人相关联的记录，而不是null。
• 我在逻辑周围贴了一个case语句来计算百分比，因为涉及除法并且除数可能为 0，从而导致除以零的错误。 此案例语句确保在这种情况下我们返回 100%;并且仅在我们免受此异常影响的情况下使用计算。
• 最后，我坚持100.0 *而不是100 *，以确保我们的解决方案可以返回非整数结果;也就是说，所以我们不会被截断到0位小数。

---

但是，您的设计也存在问题。 它假设您去过的每个地方都列在"要去的地方"表中。 如果这个假设是正确的，你最好有一个表供places_to_go，并有一个字段来标记你是否去过。 这样，您就可以在代码中强制实施该规则，提高性能并减少空间。

即可运行的示例

create table places_to_go
(
place_id bigint not null foreign key references places(place_id)
, person_id bigint not null foreign key references people(person_id)
, have_been bit not null default (0)
--& indexes / primary key field for this table / whatever else as required
)
select ppl.person_id
, coalesce(ptg.cnt_to_go,0) as 'Num_To_Go'
, coalesce(ptg.cnt_have_been,0) as 'Num_Visited'
, case 
when coalesce(ptg.cnt_to_go,0) = 0 then null --avoid /0 error ; 
else 100.0 * ptg.cnt_have_been / ptg.cnt_to_go
end 'Perc_Visited'
from people ppl
left outer join 
(
select person_id
, count(1) cnt_to_go
, count(case when have_been = 1 then 1 end) cnt_have_been
from place_to_go
group by person_id
) ptg 
on ptg.person_id = ppl.person_id

Answer 5

将查询包装在派生表中。对其结果进行最终计算：

select person_id, [Num_To_Go], [Num_Visited],
[Num_To_Go] * 100.0 / [Num_Visited]​ ​AS​ ​[Perc_Visited]
from
(
SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as [Num_To_Go],
(SELECT COUNT(*) from place_been where people.person_id=person_id) as [Num_Visited]
FROM people
) dt

或者有一个 CTE(公用表表达式(：

with cte as
(
SELECT person_id,
(SELECT COUNT(*) from place_to_go where people.person_id=person_id) as [Num_To_Go],
(SELECT COUNT(*) from place_been where people.person_id=person_id) as [Num_Visited]
FROM people
)
select person_id, [Num_To_Go], [Num_Visited],
[Num_To_Go] * 100.0 [Num_Visited] ​AS​ [Perc_Visited]
from cte