Vaqtincha funktsiyalarda ustun tartibini qanday oshirish kerak?

Abstract: I have a table with multiple columns and I am trying to implement a while (loop) function to all the columns to pull out the average of each column.

Har bir ustunga raqam kiritish uchun kodlangan (bu qo'shimcha uslubni qo'llash imkoniyatini engillashtirish uchun)

Declare @tablename as varchar(128)
Declare @column1 as varchar(128)
Delect @tablename = 'MOMENTUM_Results'

Select @column1 = sc.name
from sysobjects as so inner join syscolumns as sc on so.id = sc.id 
where so.name = @tablename and sc.colid = 2

Ushbu kodni chop qilsam, men 1 dan 122 gacha bo'lgan raqamga asoslanib ustun nomini yozadi.

Loop funktsiyasi uchun kod:

WHILE ....
BEGIN
SET @SQL = 'SELECT AVG(MR.[' + @column1 + ']) From MOMENTUM_Quintile MQ 
Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @column1 + '] = 1'
END

Lekin men bu funktsiyani aylanaga qanday integratsiya qilish kerakligini bilmayman, shuning uchun funktsiya barcha ustunlarni birma-bir silkitib, shuning uchun ba'zi tavsiyalarni qidiradi.

Rahmat!

R.H.

enter image description here

enter image description here

1
1300 satr. Va ishlashi tegishli bo'lmasligi mumkin
qo'shib qo'ydi muallif Robin_Hcp, manba
Men ustun nomlarini oldindan bilaman, men har bir so'zi ostida aylanish jarayonini osonlashtirdim
qo'shib qo'ydi muallif Robin_Hcp, manba
Men ustun nomlarini oldindan bilaman, men har bir so'zi ostida aylanish jarayonini osonlashtirdim
qo'shib qo'ydi muallif Robin_Hcp, manba
Qancha satrlar bor? Ishlash bilan bog'liqmi?
qo'shib qo'ydi muallif Shnugo, manba
Bundan tashkari: Siz buni butunlay umumiy tarzda talab qilasizmi yoki ustun nomlarini oldindan bilasizmi?
qo'shib qo'ydi muallif Shnugo, manba

6 javoblar

  1. First get all the column names in that table into a temporary table(#temp)
  2. Get the count of all columns(@count_of_columns) in that temptable.
  3. declare a variable @count=1
  4. Declare while loop as While(@count<[email protected]_of_columns)

--> fetch the column name one after another inside this while loop using the following query

SET @column1=(select top 1 column1 from 
(select Row_number()over (order by column1) as r_n_n, column1                  
from #temp) aa 
where r_n_n >[email protected])

--> use your actual code here

--> increment the @count variable

set @count = @count + 1
1
qo'shib qo'ydi
  1. First get all the column names in that table into a temporary table(#temp)
  2. Get the count of all columns(@count_of_columns) in that temptable.
  3. declare a variable @count=1
  4. Declare while loop as While(@count<[email protected]_of_columns)

--> fetch the column name one after another inside this while loop using the following query

SET @column1=(select top 1 column1 from 
(select Row_number()over (order by column1) as r_n_n, column1                  
from #temp) aa 
where r_n_n >[email protected])

--> use your actual code here

--> increment the @count variable

set @count = @count + 1
1
qo'shib qo'ydi

sys.Columns dan WHILE <body> <body> kodini tanlaysizmi? Shunga o'xshash narsa...

DECLARE @TABLENAME AS VARCHAR(128);
DECLARE @COLUMN1 AS VARCHAR(128);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 1;

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

END
;

Ehtimol sizning savolingizni to'g'ri tushunmadim, lekin siz allaqachon yozgan narsangiz bo'lsa, siz xohlagan ustun nomlarini tanlash uchun joylashtirishni o'zgartirishingiz mumkin.

Albatta siz ustunlar yoki ustunlar nomi bilan yoki sizning har qanday ...

EDIT:

Natijada so'rovni jadvalga kiritish haqida so'ragan edingiz. Agar siz allaqachon so'rovni qabul qilishga tayyor ustunli jadvalga ega bo'lsangiz, u holda javob oddiy ... Siz istagan so'rovni olib bo'lgandan keyin WHILE loopining tanasida oddiygina INSERT buyrug'i:

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    INSERT INTO  () VALUES (@SQL);
    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

Jadvalni yaratmoqchi bo'lsangiz, buni kodning boshida, boshqa hech narsani e'lon qilishdan oldin qilishingiz mumkin va WHILE davomida xuddi shunday narsani bajaring:

DECLARE @TABLENAME AS VARCHAR(128);
DECLARE @COLUMN1 AS VARCHAR(128);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 1;
CREATE TABLE  (,,);

....

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    INSERT INTO  () VALUES (@SQL);
    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;
1
qo'shib qo'ydi
Hey u erda @ 3BK?
qo'shib qo'ydi muallif Robin_Hcp, manba
qo'shib qo'ydi muallif Robin_Hcp, manba
doimiy! Yuqori kvintil o'rtacha va quyi kvintil o'rtacha qiymatini hisoblab chiqaman va umid qilamanki, 2 satr va 121 ustunli jadval yarataman.
qo'shib qo'ydi muallif Robin_Hcp, manba
Aytgancha, so'rov natijalarini jadvalga qanday kiritish mumkinligini bilasizmi? thanks @ 3BK
qo'shib qo'ydi muallif Robin_Hcp, manba
u ishladi! Men (@SQL) bajarilishda brackets qo'yish kerak edi ... thanks @ 3BK!
qo'shib qo'ydi muallif Robin_Hcp, manba
VARCHAR (MAX)
qo'shib qo'ydi muallif Robin_Hcp, manba
Men postga, bosma natija qo'shdim
qo'shib qo'ydi muallif Robin_Hcp, manba
AVG-ni tanlash (MR. [2006-12-30]) MOMENTUM_Quintile MQ-dan chapga qo'shilish MOMENTUM_RRNNN MQ-da MQ.Mnemonic = MR.Mnemonic WHERE MQ.
qo'shib qo'ydi muallif Robin_Hcp, manba
Men chop qilsam, uni ishga tushirganimda, buni menga yuboradi: "AVG [MR. [2006-12-30]) nomi MOMENTUM_Quintile MQ chapdan qo'shiling MOMENTUM_RRNNN MR MQ.Mnemonic = MR.Mnemonic WHERE MQ." tegishli identifikator emas.
qo'shib qo'ydi muallif Robin_Hcp, manba
Doimiy jadval, vaqtinchalik jadval yoki jadval o'zgaruvchisi?
qo'shib qo'ydi muallif 3BK, manba
Siz juda yoqyapsiz. Yordam berishdan xursandman.
qo'shib qo'ydi muallif 3BK, manba
Buning uchun sizni hal qilish uchun mening javobimni tahrir qildim. Ko'proq muammolar mavjudligini bilib qo'ying.
qo'shib qo'ydi muallif 3BK, manba
Salom! Menga qaytarilgan matnni ko'rsatadigan har qanday imkoniyat (faqat bir misol yaxshi bo'lishi kerak), shuning uchun muammoning nima ekanligini bilishimga ishonch hosil qilishim mumkinmi?
qo'shib qo'ydi muallif 3BK, manba
@RobinhoHcp @SQL parametri NVARCHAR (MAX) o'zgaruvchisidir yoki cheklanganmi?
qo'shib qo'ydi muallif 3BK, manba

sys.Columns dan WHILE <body> <body> kodini tanlaysizmi? Shunga o'xshash narsa...

DECLARE @TABLENAME AS VARCHAR(128);
DECLARE @COLUMN1 AS VARCHAR(128);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 1;

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

END
;

Ehtimol sizning savolingizni to'g'ri tushunmadim, lekin siz allaqachon yozgan narsangiz bo'lsa, siz xohlagan ustun nomlarini tanlash uchun joylashtirishni o'zgartirishingiz mumkin.

Albatta siz ustunlar yoki ustunlar nomi bilan yoki sizning har qanday ...

EDIT:

Natijada so'rovni jadvalga kiritish haqida so'ragan edingiz. Agar siz allaqachon so'rovni qabul qilishga tayyor ustunli jadvalga ega bo'lsangiz, u holda javob oddiy ... Siz istagan so'rovni olib bo'lgandan keyin WHILE loopining tanasida oddiygina INSERT buyrug'i:

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    INSERT INTO  () VALUES (@SQL);
    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

Jadvalni yaratmoqchi bo'lsangiz, buni kodning boshida, boshqa hech narsani e'lon qilishdan oldin qilishingiz mumkin va WHILE davomida xuddi shunday narsani bajaring:

DECLARE @TABLENAME AS VARCHAR(128);
DECLARE @COLUMN1 AS VARCHAR(128);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 1;
CREATE TABLE  (,,);

....

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    INSERT INTO  () VALUES (@SQL);
    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;
1
qo'shib qo'ydi
Hey u erda @ 3BK?
qo'shib qo'ydi muallif Robin_Hcp, manba
qo'shib qo'ydi muallif Robin_Hcp, manba
doimiy! Yuqori kvintil o'rtacha va quyi kvintil o'rtacha qiymatini hisoblab chiqaman va umid qilamanki, 2 satr va 121 ustunli jadval yarataman.
qo'shib qo'ydi muallif Robin_Hcp, manba
Aytgancha, so'rov natijalarini jadvalga qanday kiritish mumkinligini bilasizmi? thanks @ 3BK
qo'shib qo'ydi muallif Robin_Hcp, manba
u ishladi! Men (@SQL) bajarilishda brackets qo'yish kerak edi ... thanks @ 3BK!
qo'shib qo'ydi muallif Robin_Hcp, manba
VARCHAR (MAX)
qo'shib qo'ydi muallif Robin_Hcp, manba
Men postga, bosma natija qo'shdim
qo'shib qo'ydi muallif Robin_Hcp, manba
AVG-ni tanlash (MR. [2006-12-30]) MOMENTUM_Quintile MQ-dan chapga qo'shilish MOMENTUM_RRNNN MQ-da MQ.Mnemonic = MR.Mnemonic WHERE MQ.
qo'shib qo'ydi muallif Robin_Hcp, manba
Men chop qilsam, uni ishga tushirganimda, buni menga yuboradi: "AVG [MR. [2006-12-30]) nomi MOMENTUM_Quintile MQ chapdan qo'shiling MOMENTUM_RRNNN MR MQ.Mnemonic = MR.Mnemonic WHERE MQ." tegishli identifikator emas.
qo'shib qo'ydi muallif Robin_Hcp, manba
Doimiy jadval, vaqtinchalik jadval yoki jadval o'zgaruvchisi?
qo'shib qo'ydi muallif 3BK, manba
Siz juda yoqyapsiz. Yordam berishdan xursandman.
qo'shib qo'ydi muallif 3BK, manba
Buning uchun sizni hal qilish uchun mening javobimni tahrir qildim. Ko'proq muammolar mavjudligini bilib qo'ying.
qo'shib qo'ydi muallif 3BK, manba
Salom! Menga qaytarilgan matnni ko'rsatadigan har qanday imkoniyat (faqat bir misol yaxshi bo'lishi kerak), shuning uchun muammoning nima ekanligini bilishimga ishonch hosil qilishim mumkinmi?
qo'shib qo'ydi muallif 3BK, manba
@RobinhoHcp @SQL parametri NVARCHAR (MAX) o'zgaruvchisidir yoki cheklanganmi?
qo'shib qo'ydi muallif 3BK, manba

Agar ularni oldini olsa, ilmoq yoki kursorlarni ishlatmang. Protsessual fikrlash shaytoni sizni og'riq va azob-uqubatlarga chuqur tashvishlanadigan yondashuvlarning nuridan uzoqlashtiradi!

Ustun nomlarini oldindan bilaman, men faqat har bir ustun orqali aylanish jarayonini osonlashtirdim.

NO! This is not easing the way but punishing the poor engine :-)

Siz bu izlayapsizmi?

CREATE TABLE dbo.Test(ID INT, VAl1 DECIMAL(14,4), Val2 DECIMAL(14,4), Val3 DECIMAL(14,4));
GO
INSERT INTO dbo.Test VALUES(1,1,10,100),(2,2,20,200),(3,3,30,300);
GO
    SELECT AVG(Val1) AS Avg1,AVG(Val2) AS Avg2,AVG(Val3) AS Avg3
    FROM dbo.Test;
GO
DROP TABLE dbo.Test;

Natija

Avg1        Avg2        Avg3
2.000000    20.000000   200.000000

UPDATE

Agar siz faqat dangasa bo'lishingiz va yozishdan qochsangiz, quyidagi kabi harakat qilishingiz mumkin:

SELECT ',AVG(' + COLUMN_NAME + ') AS ' + QUOTENAME('AVG_' + COLUMN_NAME)   
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Test'
FOR xml PATH('')

Natija

,AVG(ID) AS [AVG_ID],AVG(VAl1) AS [AVG_VAl1],AVG(Val2) AS [AVG_Val2],AVG(Val3) AS [AVG_Val3]

Ushbu hiyla bilan siz qattiq kodli iborasini juda oson tuzishi mumkin :-)

0
qo'shib qo'ydi
Afsuski, bu emas .. Menda ikkita jadval bor (ular bir xil satrlar va ustunlar bor), lekin birja aktsiyadorlari bilan, ikkinchisi esa har bir qaytib keladigan kvintillarga ega. Men eng yuqori kvintil bo'yicha qaytib kelgan o'rtacha qiymatini hisoblash uchun chapga qo'shildim, shuning uchun har bir ustunni bosib o'tadigan vazifani o'rtacha hisoblab chiqaman
qo'shib qo'ydi muallif Robin_Hcp, manba
qo'shib qo'ydi muallif Shnugo, manba

Agar ularni oldini olsa, ilmoq yoki kursorlarni ishlatmang. Protsessual fikrlash shaytoni sizni og'riq va azob-uqubatlarga chuqur tashvishlanadigan yondashuvlarning nuridan uzoqlashtiradi!

Ustun nomlarini oldindan bilaman, men faqat har bir ustun orqali aylanish jarayonini osonlashtirdim.

NO! This is not easing the way but punishing the poor engine :-)

Siz bu izlayapsizmi?

CREATE TABLE dbo.Test(ID INT, VAl1 DECIMAL(14,4), Val2 DECIMAL(14,4), Val3 DECIMAL(14,4));
GO
INSERT INTO dbo.Test VALUES(1,1,10,100),(2,2,20,200),(3,3,30,300);
GO
    SELECT AVG(Val1) AS Avg1,AVG(Val2) AS Avg2,AVG(Val3) AS Avg3
    FROM dbo.Test;
GO
DROP TABLE dbo.Test;

Natija

Avg1        Avg2        Avg3
2.000000    20.000000   200.000000

UPDATE

Agar siz faqat dangasa bo'lishingiz va yozishdan qochsangiz, quyidagi kabi harakat qilishingiz mumkin:

SELECT ',AVG(' + COLUMN_NAME + ') AS ' + QUOTENAME('AVG_' + COLUMN_NAME)   
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Test'
FOR xml PATH('')

Natija

,AVG(ID) AS [AVG_ID],AVG(VAl1) AS [AVG_VAl1],AVG(Val2) AS [AVG_Val2],AVG(Val3) AS [AVG_Val3]

Ushbu hiyla bilan siz qattiq kodli iborasini juda oson tuzishi mumkin :-)

0
qo'shib qo'ydi
Afsuski, bu emas .. Menda ikkita jadval bor (ular bir xil satrlar va ustunlar bor), lekin birja aktsiyadorlari bilan, ikkinchisi esa har bir qaytib keladigan kvintillarga ega. Men eng yuqori kvintil bo'yicha qaytib kelgan o'rtacha qiymatini hisoblash uchun chapga qo'shildim, shuning uchun har bir ustunni bosib o'tadigan vazifani o'rtacha hisoblab chiqaman
qo'shib qo'ydi muallif Robin_Hcp, manba
qo'shib qo'ydi muallif Shnugo, manba