22,207
社区成员
发帖
与我相关
我的任务
分享
SELECT id, Quartile = nt - 1, b = MIN(b)
FROM (
SELECT id, b,
nt = NTILE(4) OVER(PARTITION BY id ORDER BY b)
FROM ss
) tmp
GROUP BY id, nt
UNION ALL
SELECT id, Quartile = 4, b = MAX(b)
FROM ss
GROUP BY id
ORDER BY id, Quartile
/* Result
id Quartile b
----------- -------------------- -----------
1 0 6
1 1 36
1 2 41
1 3 47
1 4 49
2 0 7
2 1 36
2 2 40
2 3 41
2 4 41
*/
CREATE TABLE ss(id INT ,b INT)
INSERT ss SELECT 1,6
UNION ALL SELECT 1,47
UNION ALL SELECT 1, 49
UNION ALL SELECT 1, 15
UNION ALL SELECT 1, 42
UNION ALL SELECT 1, 41
UNION ALL SELECT 1, 7
UNION ALL SELECT 1, 39
UNION ALL SELECT 1, 43
UNION ALL SELECT 1, 40
UNION ALL SELECT 1, 36
UNION ALL SELECT 2,7
UNION ALL SELECT 2, 15
UNION ALL SELECT 2, 36
UNION ALL SELECT 2, 39
UNION ALL SELECT 2, 40
UNION ALL SELECT 2, 41
CREATE FUNCTION Get_A(@id INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @s TABLE(id INT IDENTITY(1,1),x INT )
DECLARE @Num INT
DECLARE @q0 DECIMAL(20,4),@q1 DECIMAL(20,4),@q2 DECIMAL(20,4),@q3 DECIMAL(20,4),@q4 DECIMAL(20,4)
DECLARE @q1p DECIMAL(10,2) ,@q2p DECIMAL(10,2),@q3p DECIMAL(10,2)
INSERT @s SELECT b FROM ss WHERE id=@id ORDER BY b
SET @Num=@@ROWCOUNT
SELECT @q1p=(@Num+1)*1.0/4,@q2p=(@Num+1)*1.0/2,@q3p=3*(@Num+1)*1.0/4
IF @Num %2=0
BEGIN
SELECT @q0=MIN(x),
@q1= (SELECT x FROM @s WHERE id=FLOOR(@q1p))+
(SELECT SUM(CASE WHEN id=FLOOR(@q1p) THEN -x ELSE x end) FROM @s WHERE id IN(FLOOR(@q1p),CEILING(@q1p)))*(@q1p-FLOOR(@q1p)),
@q2= (SELECT x FROM @s WHERE id=FLOOR(@q2p))+
(SELECT SUM(CASE WHEN id=FLOOR(@q2p) THEN -x ELSE x end) FROM @s WHERE id IN(FLOOR(@q2p),CEILING(@q2p)))*(@q2p-FLOOR(@q2p)),
@q3= (SELECT x FROM @s WHERE id=FLOOR(@q3p))+
(SELECT SUM(CASE WHEN id=FLOOR(@q3p) THEN -x ELSE x end) FROM @s WHERE id IN(FLOOR(@q3p),CEILING(@q3p)))*(@q3p-FLOOR(@q3p)),
@q4= MAX(x)
FROM @s
END
ELSE
BEGIN
SELECT @q0=MIN(x),
@q1=MAX(CASE WHEN id=@q1p THEN x ELSE 0 END),
@q2=MAX(CASE WHEN id=@q2p THEN x ELSE 0 END),
@q3=MAX(CASE WHEN id=@q3p THEN x ELSE 0 END),
@q4=MAX(x)
FROM @s
END
RETURN LTRIM(@q0)+','+LTRIM(@q1)+','+LTRIM(@q2)+','+LTRIM(@q3)+','+LTRIM(@q4)
END
GO
SELECT id, dbo.get_a(id) 四分位数 FROM ss
GROUP BY id
--result
/*id 四分位数
----------- --------------------------------------------------
1 6.0000,15.0000,40.0000,43.0000,49.0000
2 7.0000,13.0000,37.5000,40.2500,41.0000
(所影响的行数为 2 行)
*/
SELECT MAX(TaxRate) --求最大值?
FROM Sales;
GO