什么是最好的和最快的方式来获取行号

Question

假设我的表格中有以下格式的数据，我必须提取十分位数，那么什么是SQL服务器中最好和最快的查询来获得正确的十分位数。

想我已经在我的标量函数的两个参数，

1. PerformanceValue
2. Measureid

想我通过在性能值参数11.22和3 measureid，标量函数返回3

假设我在性能值参数中通过了85.54，在度量值为4时返回了10

想我通过在性能参数值54.00和4 measureid它返回7.2

Answer 1

您所需要的表包含MeasureID，数量范围（例如from_value和till_value）和Decile。然后使用简单的WHERE子句：

select decile 
from mytable 
where measureid = 4 
    and 54.00 between from_value and till_value; 

Answer 2

正如其他人所提到的。最好的行动方式是纠正餐桌设计。当然，我们都生活在现实世界中，并不总是可能。

以下内容在tempdb中使用“semi-perminant”表。这将使用新测量进行更新，而不必在每次执行时重新处理基础表。这应该会产生尽可能快的执行时间，而不是重新设计整个表格进行重新设计。

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData; 

CREATE TABLE #TestData (
    Measured INT NOT NULL PRIMARY KEY CLUSTERED, 
    Decile_3 VARCHAR(15) NOT NULL, 
    Decile_4 VARCHAR(15) NOT NULL, 
    Decile_5 VARCHAR(15) NOT NULL, 
    Decile_6 VARCHAR(15) NOT NULL, 
    Decile_7 VARCHAR(15) NOT NULL, 
    Decile_8 VARCHAR(15) NOT NULL, 
    Decile_9 VARCHAR(15) NOT NULL, 
    Decile_10 VARCHAR(15) NOT NULL 
    ); 
INSERT #TestData (Measured, Decile_3, Decile_4, Decile_5, Decile_6, Decile_7, Decile_8, Decile_9, Decile_10) VALUES 
    (1, '54.67 - 35.91', '35.90 - 25.63', '25.62 - 19.34', '19.33 - 14.15', '14.14 - 9.10', ' 9.09 - 3.34', ' 3.33 - 0.01', '0'), 
    (2, '53.85 - 64.74', '64.75 - 70.90', '70.91 - 86.68', '89.32 - 92.92', '92.91 - 96.54', '96.55 - 98.67', '96.55 - 98.67', '>= 98.68'), 
    (3, '11.22 - 18.57', '18.58 - 24.99', '25.00 - 31.84', '31.85 - 38.92', '38.93 - 47.86', '47.87 - 59.99', '60.00 - 79.01', '>= 79.01'), 
    (4, '14.13 - 23.25', '23.26 - 33.02', '33.03 - 43.58', '43.59 - 53.96', '53.97 - 63.60', '63.61 - 74.54', '74.55 - 85.52', '>= 85.53'), 
    (5, '12.41 - 22.21', '22.22 - 32.30', '32.31 - 40.86', '40.87 - 47.91', '47.92 - 55.25', '55.26 - 63.06', '63.07 - 73.22', '>= 73.23'); 

-- SELECT * FROM #TestData td' 

--====================================================================== 
--====================================================================== 

-- create a semi-perminant unpivot table in temp db that simply adds new measurement as opposed to 
-- reporcessing the entire base table with every execution. 
IF OBJECT_ID('tempdb.dbo.UnpivotData', 'U') IS NULL 
BEGIN -- DROP TABLE tempdb.dbo.UnpivotData; 
    CREATE TABLE tempdb.dbo.UnpivotData (
     Measured INT NOT NULL, 
     dType TINYINT NOT NULL, 
     BegRange DECIMAL(9,2) NOT NULL, 
     EndRange DECIMAL(9,2) NOT NULL, 
     PRIMARY KEY CLUSTERED (Measured, dType) 
     ); 
END; 
INSERT tempdb.dbo.UnpivotData (Measured, dType, BegRange, EndRange) 
SELECT 
    td.Measured, 
    d.dType, 
    r.BegRange, 
    r.EndRange 
FROM 
    #TestData td 
    CROSS APPLY (VALUES (3, td.Decile_3), (4, td.Decile_4), (5, td.Decile_5), (6, td.Decile_6), 
          (7, td.Decile_7), (8, td.Decile_8), (9, td.Decile_9), (10, td.Decile_10) 
          ) d (dType, dValue) 
    CROSS APPLY (VALUES (
         CASE 
          WHEN CHARINDEX(' - ', d.dValue) > 0 THEN LEFT(d.dValue, 5) 
          WHEN LEFT(d.dValue, 2) = '>=' THEN RIGHT(d.dValue, 5) 
          WHEN LEFT(d.dValue, 2) = '<=' THEN '0' 
          ELSE d.dValue 
         END, 
         CASE 
          WHEN CHARINDEX(' - ', d.dValue) > 0 THEN RIGHT(d.dValue, 5) 
          WHEN LEFT(d.dValue, 2) = '>=' THEN '9999999.99' 
          WHEN LEFT(d.dValue, 2) = '<=' THEN RIGHT(d.dValue, 5) 
          ELSE d.dValue 
         END 
         )) r (BegRange, EndRange) 
WHERE 
    NOT EXISTS (SELECT * FROM tempdb.dbo.UnpivotData ud WHERE td.Measured = ud.Measured); 

----------------------------------------------------------- 

-- Querieng from the temp table is now quite easy. 
DECLARE 
    @Measure INT = 4, 
    @PerformanceVal DECIMAL(9,2) = 85.54; 

SELECT 
    ud.Measured, 
    ud.dType, 
    ud.BegRange, 
    ud.EndRange 
FROM 
    tempdb.dbo.UnpivotData ud 
WHERE 
    ud.Measured = @Measure 
    AND @PerformanceVal BETWEEN ud.BegRange AND ud.EndRange; 

结果...

Measured dType BegRange        EndRange 
----------- ----- --------------------------------------- --------------------------------------- 
4   10 85.53         9999999.99 

Answer 3

下面是最终可能更快，因为它开始做对Measurid索引查找，以便需要被解析只有数据的单个行的另一种选择。 ..

DECLARE 
    @Measureid INT = 4729, 
    @Value DECIMAL(9,2) = 85.0; 

IF OBJECT_ID('tempdb..#UnpivotData', 'U') IS NOT NULL 
DROP TABLE #UnpivotData; 

SELECT 
    td.Measureid, 
    d.dType, 
    BegRange = ISNULL(CAST(r.BegRange AS DECIMAL(9,2)), 0), 
    EndRange = ISNULL(CAST(r.EndRange AS DECIMAL(9,2)), 0) 
    INTO #UnpivotData 
FROM 
    #TestData td 
    CROSS APPLY (VALUES (3, td.Decile_3), (4, td.Decile_4), (5, td.Decile_5), (6, td.Decile_6), 
          (7, td.Decile_7), (8, td.Decile_8), (9, td.Decile_9), (10, td.Decile_10) 
          ) d (dType, dValue) 
    CROSS APPLY (VALUES (
         CASE 
          WHEN CHARINDEX(' - ', d.dValue) > 0 THEN LEFT(d.dValue, 5) 
          WHEN LEFT(d.dValue, 2) = '>=' THEN RIGHT(d.dValue, 5) 
          WHEN LEFT(d.dValue, 2) = '<=' THEN '0' 
          ELSE d.dValue 
         END, 
         CASE 
          WHEN CHARINDEX(' - ', d.dValue) > 0 THEN RIGHT(d.dValue, 5) 
          WHEN LEFT(d.dValue, 2) = '>=' THEN '9999999.99' 
          WHEN LEFT(d.dValue, 2) = '<=' THEN RIGHT(d.dValue, 5) 
          ELSE d.dValue 
         END 
         )) r (BegRange, EndRange) 
WHERE 
    td.Measureid = @Measureid; 

------------------------------------------------------ 

SELECT 
    ud.Measureid, 
    ud.dType, 
    ud.BegRange, 
    ud.EndRange 
FROM 
    #UnpivotData ud 
WHERE 
    @Value BETWEEN ud.BegRange AND ud.EndRange; 
GO