如何计算SQL中的百分位数差异？

Let's say you have 2 tables.

假设你有2张桌子。

Table 1 - T
Columns
City ID(Foreign keyed to C.cityid)
Completed (yes or no)
NumberOne (integer)
NumberTwo (integer)
Date (timestamp with timezone)

Table 2 - C
City ID (integer)
City Name (string)

For each of the cities 'A' and 'B', how do I calculate 90th percentile difference between NumberOne and NumberTwo for all completed things within the last 30 days.

对于每个城市'A'和'B'，如何计算过去30天内所有已完成事物的NumberOne和NumberTwo之间的第90百分位数差异。

Here's what I have so far:

这是我到目前为止所拥有的：

SELECT  
    100.0*(T.NumberOne - T.NumberTwo) / T.NumberTwo As PercentDiff
FROM T
JOIN C ON CityID

I'm new to SQL and not sure how to create one query for all of this. Do I need to use subqueries?

我是SQL新手，不知道如何为所有这些创建一个查询。我需要使用子查询吗？

2 个解决方案

#1

Try something like this:

尝试这样的事情：

SELECT 
  100.0*(t.NumberOne - t.NumberTwo) / t.NumberTwo As PercentDiff
from T t
inner join C c
on t.[City ID] = c.[City ID]
where c.Name in ('A','B') 
and t.Completed = 'yes'
and t.Date > DATEADD(day, -30, GETDATE())

#2

Try ON CITYID = CITYID The in statement expects a Boolean expression.

尝试ON CITYID = CITYID in语句需要一个布尔表达式。

#1