在 Oracle 中获取最新作业数据的更好方法

Question

我有两个我目前正在使用的表：

国家

ID  Name                       ShortName
1   France                     FR
2   United Kingdom             UK
3   United States Of America   USA
4   Argentina                  AG
5   Portugal                   PO
6   Spain                      SP
7   Italy                      IT

作业数据

JobID  CountryID TotalPercentage Indicator TimeStamp
500    1         78.9            NEW       10-06-2018
501    1         93.2            NEW       11-06-2018
509    1         91.11           NEW       02-07-2018
508    2         72.5            NEW       02-07-2018
502    2         88.57           NEW       12-06-2018
503    4         46.67           NEW       12-06-2018
506    4         91.43           NEW       29-06-2018
507    2         53.33           NEW       01-07-2018
504    1         50              NEW       14-06-2018
505    4         44.4            NEW       15-06-2018
501    1         0               OLD       11-06-2018
506    4         40              OLD       29-06-2018
508    2         78              OLD       02-07-2018

我编写了以下查询来返回我的数据，如下所示，即仅显示给定国家/地区的最新作业 ID 的值：

Select (Select Name from Country where ID = T1.CountryID) as CountryName , 
(Select ShortName from Country where ID = T1.CountryID) as CountryShortName , 
Coalesce(Round(T1.TotalPercentage,2),0) As TrueCount , 
(Case
When Coalesce(Round(T1.TotalPercentage,2),0) = 0
Then Coalesce(Round(T1.TotalPercentage,2),0)
Else 100-Coalesce(Round(T1.TotalPercentage,2),0)
End) As Falsecount
FROM JobData T1 LEFT OUTER JOIN JobData T2 
ON (T1.CountryId = T2.CountryId AND T1.JobId < T2.JobId)
WHERE T2.CountryId IS NULL AND Indicator = 'NEW'; 

CountryName      CountryShortName  TrueCount  FalseCount
France           FR                91.11      8.89
United Kingdom   UK                72.5       27.5
Argentina        AG                91.43      8.57

我知道我正确获得了上述结果，但是有没有更好的方法和更快的方法来获得所需的输出？

提前谢谢。

Answer 1

您可以JOIN表COUNTRY以避免对此表进行多次查询( Nrows * 2 列 (

另一种方法是分析查询：

SELECT CountryName
, CountryShortName
, TrueCount
, Falsecount
FROM   ( SELECT c.NAME                                    AS CountryName
, c.ShortName                               AS CountryShortName
, Coalesce(Round(T1.TotalPercentage,2),0) As TrueCount , 
(Case
When Coalesce(Round(T1.TotalPercentage,2),0) = 0
Then Coalesce(Round(T1.TotalPercentage,2),0)
Else 100-Coalesce(Round(T1.TotalPercentage,2),0)
End) As Falsecount
, ROW_NUMBER () OVER ( partition BY t1.CountryId ORDER BY T1.JobId DESC) rn
FROM   JobData T1
JOIN   Country c
ON c.ID = T1.CountryID
WHERE  t1.Indicator = 'NEW' ) t
WHERE  rn = 1; 

我建议你用最容易维护/理解的给你，最快的是需要的

http://sqlfiddle.com/#!4/f069dd/8

Answer 2

我喜欢CROSS APPLY，因为它允许您在连接中使用ORDER BY ... FETCH FIRST 1 ROW ONLY。

以下查询将为您提供每个国家/地区以及每个国家/地区的最新NEW工作信息。 然后，您可以根据上面的查询要求进一步调整结果，您没有解释，我没有花时间理解。

SELECT c.*, j.* 
FROM countries c 
CROSS APPLY ( SELECT * 
FROM jobdata j2 
WHERE j2.countryId = c.id 
AND j2.indicator = 'NEW' 
ORDER BY j2.timestmp DESC
FETCH FIRST 1 ROW ONLY ) j
+----+----------------+-----------+-------+-----------+-----------------+-----------+-----------+
| ID |      NAME      | SHORTNAME | JOBID | COUNTRYID | TOTALPERCENTAGE | INDICATOR | TIMESTMP  |
+----+----------------+-----------+-------+-----------+-----------------+-----------+-----------+
|  1 | France         | FR        |   509 |         1 |           91.11 | NEW       | 02-JUL-18 |
|  2 | United Kingdom | UK        |   508 |         2 |            72.5 | NEW       | 02-JUL-18 |
|  4 | Argentina      | AG        |   506 |         4 |           91.43 | NEW       | 29-JUN-18 |
+----+----------------+-----------+-------+-----------+-----------------+-----------+-----------+