Блог

У меня есть таблица с именами и некоторыми значениями. Я хочу передать значения, связанные с именами, в другую таблицу, чтобы предварительно заполнить список значений. Я не уверен, что лучший способ подойти к этому — создать функцию или процедуру.

Ниже приведен запрос, который у меня есть сейчас, где я должен УСТАНОВИТЬ переменные вручную. Но вместо того, чтобы делать это вручную, я хотел бы передать значения из другой таблицы в эту. Как мне это сделать?

Например, у меня есть таблица с именем ABC, и в этой таблице 3 значения

 Name|AVG |DEV
A   |1.89|.74
B   |2.43|1.20
C   |.74 |.12
 

Я хочу передать все значения из этой таблицы в приведенном ниже запросе. Среднее значение в таблице преобразуется в переменную @AVG в приведенном ниже запросе, а значение DEV в таблице — в таблицу @deviation ниже.

Результаты могут быть помещены в новую таблицу или в запрос.

 DECLARE @avg DECIMAL(4,1) = 1.89 --this would be row 1 (value A) avg
DECLARE @deviation DECIMAL(4,1) = 0.74 --this would be row 1 (value A) DEV


DECLARE @startnum DECIMAL(4,1)= @Avg - (@deviation * 3)
DECLARE @endnum DECIMAL(4,1)= @Avg   (@deviation * 3)



;
WITH gen AS (
    SELECT CAST(@startnum AS decimal(4,1)) AS Mulitiple
    UNION ALL
    SELECT CAST(Mulitiple .1 AS decimal(4,1)) FROM gen WHERE Mulitiple .1<=@endnum
)
SELECT A.Mulitiple , CAST(((A.Mulitiple-@avg)/@deviation) AS DECImAL(4,2)) AS  ZScore
,Z.Y AS Area
FROM gen AS A
LEFT JOIN STAT..ZScore AS Z ON CAST(((A.Mulitiple-@avg)/@deviation) AS DECImAL(4,2)) = CAST(Z.Zscore AS decimal(4,2))
--WHERE Z.Y IS NOT NULL 
option (maxrecursion 10000)
 

Этот подход использует функцию подсчета с именем dbo.fnNumbers для генерации диапазона zscores.

dbo.fnNumbers

 create function [dbo].[fnNumbers](
  @zero_or_one   bit, 
  @n             bigint)
returns table with schemabinding as return
with n(n) as (select null from (values (1),(2),(3),(4)) n(n))
select 0 n where @zero_or_one = 0
union all
select top(@n) row_number() over(order by (select null)) n
from n na, n nb, n nc, n nd, n ne, n nf, n ng, n nh,
     n ni, n nj, n nk, n nl, n nm, n np, n nq, n nr;
 

Запрос

 drop table if exists #abc;
go
create table #abc(
   [name]   varchar(2),
   [avg]    decimal(4,1),
   [dev]    decimal(4,1));

insert #abc([name], [avg], [dev]) values
('a', 1.89,  .74),
('b', 2.43, 1.20),
('c', 0.74,  .12);

select a.[name], gen.multiple, gen_z.zscore, z.y as area
from #abc a
     cross apply dbo.fnNumbers(1, cast(2*(a.dev*3) 0.1 as decimal(4,1))*10) fn
     cross apply (values (cast((a.[avg]-(a.dev*3) (fn.N-1)*0.1) as decimal(4,1)))) gen(multiple)
     cross apply (values (cast(((gen.multiple-a.[avg])/a.dev) AS decimal(4,2)))) gen_z(zscore)
     left join stat..zscore z on gen_z.zscore=cast(z.zscore as decimal(4,2))
order by a.[name], gen.multiple;
 

Если я правильно понимаю, вы можете просто использовать JOIN . В этом случае вы можете ввести таблицу с помощью CROSS JOIN :

 SELECT ABC.*, A.Mulitiple ,
       CAST(((A.Mulitiple - ABC.avg) / ABC.dev) AS DECImAL(4, 2)) AS  ZScore
,Z.Y AS Area
FROM ABC CROSS JOIN
     gen a LEFT JOIN
     STAT..ZScore Z
     ON CAST(((A.Mulitiple - abc.avg)/ abc.dev) AS DECIMAL(4,2)) = CAST(Z.Zscore AS decimal(4,2))
--WHERE Z.Y IS NOT NULL 
 

Вы можете избежать использования формулы дважды, используя:

 SELECT ABC.*, A.Mulitiple, v.ZScore, Z.Y AS Area
FROM ABC CROSS JOIN
     gen a CROSS APPLY
     (VALUES (CAST((A.Mulitiple - ABC.avg) / ABC.dev AS DECImAL(4, 2))
     ) v(Zscore) LEFT JOIN
     STAT..ZScore Z
     ON v.ZScore = CAST(Z.Zscore AS decimal(4,2))
--WHERE Z.Y IS NOT NULL