نحوه ی count و sum‌ گیری از محتویات یك select

[majid_darab]

با سلام و عرض خسته نباشید :
من یك select دارم كه خروجی آن قاعدتاً یك table‌ می باشد.
می خواهم در قالب یك select دیگر تعداد ركورد های table بالا و مجموع نهایی یكی از فیلدهای Table بالا رابدست بیاوم.
اما هر كاری كردم با مشكل syntax روبرو شدم.
اگر در این زمینه راهنمایی بفرمایید ممنون می شوم.

با تشكر
مجید داراب

[بهنام بهمنی]

لطفا متن Select ي که خطا بر می گرداند, را اينجا قرار دهید

[morteza271]

فکر کنم با این روش بشه این کارو انجام داد...

ابتدا جدول خروجی select رو داخل یه جدول مجازی(دید) بریزی و سپس از روی جدول مجازی COUNT و SUM بگیری:
اینم کد:

create view person1 as
select name, phone, salary
from person

select COUNT(name), SUM(salary)
from person1

البته گفتم که این روش نظر خودمه و مطمئنا روش های دیگه ای هم وجود داره:متفکر:
موفق باشید...

[بهزادصادقی]

اگر من درست متوجه منظور شما شده باشم، شاید این به شما کمک کنه.

فرض کنید که select اولیه شما این است:



select
o.name,
o.type,
convert( decimal(20,0), o.object_id ) ObjectIDValue
from
sys.objects o;


حالا شما می خواهید تعداد سطر های این select و مجموع مقادیر ستون ObjectIDValue را به دست بیاورید. می توانید از مفهموی به اسم derived table استفاده نمائید. کل کد select اولی را می تونید قرار دهید درون یک پرانتز و بعد از پرانتز به آن یک اسم خاص بدهید، که بهش می گویند یک alias. مثل این:



(
select
o.name,
o.type,
convert( decimal(20,0), o.object_id ) ObjectIDValue
from
sys.objects o
) MyFirstSelect


اینجا من یک alias به این select داده ام و آن را با استفاده از آن alias در حقیقت نام گذاری کرده ام . نامی را هم که روی آن گذاشته ام MyFirstSelect می باشد. (در زبان انگلیسی، کلمه alias معنی «شهرت» یا «اسم مستعار» را دارد.)

به اینجا که رسیدید، حالا می توانید از این derived table طوری استفاده کنید مثل اینکه خودش یک table است که اسمش همان alias ی می باشد که شما برایش انتخاب کردید. . پس query نهایی شما میشود چیزی مثل این:



select
sum( MyFirstSelect.ObjectIDValue ) SumOfObjectIDValues,
count(1) CountOfObjects
from
(
select
o.name,
o.type,
convert( decimal(20,0), o.object_id ) ObjectIDValue
from
sys.objects o
) MyFirstSelect;



کد بالا باید در SQL Server Management Studio قابل اجرا باشد.

[majid_darab]

با تشكر از تمامی دوستان :
آنچه كه در پست قبلی عنوان گردید دقیقاً موردی است كه مد نظر بود . (derived Table)
فقط اگر مقدور است توضیح كوچكی در مورد expression داخل count مرقوم فرمایید كه چرا عدد یك داخل آن قرار گرفته است .
من كد بالا رو در sql management تست كردم و به جای این عدد یك هر عددی كه بزاری درست كار می كنه...
من فكر می كردم در داخل count به عنوان پارامتر فقط باید star قرار می گرفت.

با تشكر
مجید داراب

[بهزادصادقی]

البته همان طور که خود واقف هستید، count یک پارامتر نیست. یک تابع است. این شاید اشکلال جرئی ای باشد، ولی بد نیست که از واژه ها درست استفاده کنیم تا بعدا به مشکل بر نخوریم.

وقتی که شما درون پرانتزهای count یک expression غیر متغیر را می گذارید، مثلا 1، 5، 'Hello'، یا هر مقدار constant دیگر، دارید به SQL Server دستور می دهید که در مرحله اول، یک ستون مجازی جدید را در مجموعه نتیجه های select حاضر لحاظ کند، در مرحله دوم، ارزش آن ستون را برای هر کدام از سطرهای مجموعه نتایج select مساوی قرار دهد با ارزش آن مقدار constant ای که شما در درون پرانتز های count لحاظ نموده اید، و در مرحله سول و آخر، تعداد بارهایی را که آن ستون مقدار گرفته را بشمارد.

به هر حال، این فرق ندارد که چه مقداری را ازش استفاده می کنید. هر مقداری که باشد، تعداد بارهایی که آن ستون مجازی آن مقدار را می گیرد برابر خواهد بود با تعداد سطرهای موجود در مجموعه نتایج آن select. پس هر دو select ذیل، جوابشان یکی خواهد بود:



select
count( 1 )
from
sys.objects o;


select
count( 'Salam' )
from
sys.objects o;


ولی اگر expression استفاده شده در درون count یک مقدار متغیر بود، چی؟ اینجا، چیزی که می تواند برای هر سطر از یک مجموعه نتایج select تغییر کند (متغییر باشد) یک ستون از یکی از جدول های select می باشد. پس فرض کنید من در خواست چنین query ای را می کنم:



select
count( o.name )
from
sys.objects o;


وقتی که شما درون پرانتزهای count یک ستون را لحاظ می نمائید، دارید به SQL Server دستور می دهید که در مرحله اول، یک ستون مجازی جدید را در مجموعه نتیجه های select حاضر لحاظ کند، در مرحله دوم، ارزش آن ستون را برای هر کدام از سطرهای مجموعه نتایج select مساوی قرار دهد با ارزش آن ستونی که شما در درون پرانتز های count لحاظ نموده اید، و در مرحله سول و آخر، تعداد بارهایی را که آن ستون مقدار گرفته را بشمارد. برای انحام این کار، SQL Server مجبور است با مراجعه به دیسک، ارزش ستون مورد نظر را در تک تک سطرهای جدول مورد نظر را استخراج کرده، آنها را در قالب یک ستون مجازی به نتایج select اضافه نموده، و بعد تعداد بارهایی که آن ستون مجازی مقدار گرفته است را بشمارد.

خوب، جواب این همه کار بالاخره دقیقا برابر خواهد بود با تعداد سطرهای مجموعه نتایج آن دستور select.

حال، فرض کنید که درون پرانتز تابع count، شما از ستاره استفاده می کنید. انحام این کار به SQL Server این دستور را می دهد:

1. به تعداد کل ستون هایی که در کل جداول استفاده شده در این دستور select وجود دارند، ستون های مجازی خاصی را به مجموعه نتایج حاصله از این select اضافه کن.

2. هر یک از این ستون های مجازی را متصل کن به یک ستون خاص از یک جدول خاص استفاده شده در select.

3. کل این ستون های مجازی را برای تک تک سطرهای مجموعه نتایج select پر کن: با مراجعه به اصل جدول که در روی دیسک وجود دارذ، مقدار آن ستون را بکش بیرون از جدول، و بعد بیا آن را توی مجموعه نتایج select وارد کن.

4. بعد از اتمام این کار، وقتی که نیایج select حاضر شده اند، به من بگو که کلا چند بار سطرهایی که حاوی این ترکیب مختلف از ستون های مجازی بوده اند در این عملیاتی که تو الان انجام دادی مقدار گرفتند.

جواب SQL Server این بار هم دقیقا همان می باشد که بارهای قبلی بود. یعنی به تعداد سطر های موجود در مجموعه نتایج جواب آن دستور select.

پس، اگر شما می خواهید بدانید که دستور select بالا چند سطر نتیجه تولید می کند، شما می توانید از کل این expression ها استفاده نمائید:



count(1)
count('a')
count(o.name)
count(*)


همه موارد بالا جواب شما را خواهند داد. جواب شما فرقی نخواهد کرد. ولی مقدار کاری که SQL Server باید انجام دهد تا به آن جواب برسد از زمین تا آسمان فرق خواهد کرد. وقی در دروی پرانتزهای count شما از علامت ستاره استفاده می کنید، SQL Server می آید و تمام ستون های مربوطه را از توی تمام جداول مربوطه می خواند. زمانی که تعداد سطرهای موجود در جداول شما زیاد است، این می تواند بار بسیار بزرگی را روی سرور شما بگذارد.

در نتیجه، من پیشنهاد می کنم هرگز از ستاره استفاده نکنید. همان جواب را با سرعت بسیار بالاتر و پردازش خیلی کم تر می توانید با استفاده از یک مقدار غیر متغیر بگیرید.

[majid_darab]

در ابتدا ازاینكه با صبر و حوصله به طور كامل پاسخگوی مورد مطرح شده بودید از شما به شدت قدر دانی می كنم....

البته همان طور که خود واقف هستید، count یک پارامتر نیست. یک تابع است. این شاید اشکلال جرئی ای باشد، ولی بد نیست که از واژه ها درست استفاده کنیم تا بعدا به مشکل بر نخوریم.
منظور از پارامتر COUNT در پست قبلی من (پارامترِ COUNT = پارامتر داخل COUNT) بود.
لذا از بابت عدم رساندن مفهوم درست این دو كلمه ، از دوستان عذرخواهی میكنم و پست قبلو ویراش نمودم.

از توضیحات شما دوست عزیز نیز بهره بردیم...
با تشكر
مجید داراب

[nooshin964]

دوست عزيز . بهترين روش براي اين كار استفاده از CTE
با استفاده از دستور WITH ميباشد.

[majid_darab]

با سلام و عرض خسته نباشید :
بسیار بسیار خرسندم كه این تاپیك موجب فراگیری مطالب جدیدی در مورد Sql server گردید و امید است كه چراغ تاپیك هایی از این دست همیشه پرفروغ و درخشان باشد...

در حال حاضر مطالبی در مورد CTE و WITH عنوان گردید كه ذكر توضیحاتی در مورد آنها خالی از لطف نمی باشد.
اگر مقدور است در مورد مفاهیم نام برده شده نیز توضیحاتی مرقوم فرمایید.

با تشكر
مجید داراب

[بهزادصادقی]

تا زمان وجود SQL Server 2000، راه حلی که من در بالا به شما ارائه کردم، یعنی استفاده کردن از یک derived table، یکی از تنها راه های مناسب حل چنین مسائلی بود. ولی از SQL Server 2005 به بعد، Microsoft مفهومی را وارد زبان T-SQL کرد به نام Common Table Expressions که مخفف آن CTE می باشد. یکی از اهداف اضافه کردن CTE در حقیقت حل یک سری مشکلاتی بود که derived table ها همیشه داشته اند.

بزرگترین این مشکل ها این است که اگر query شما طوری باشد که شما احتیاج دارید در چند جای آن از یک derived table استفاده نمائید، برای این کار مجبورید که کد آن derived table را هر جا که لازم دارید از آن استفاده کنید توی query خود تکرار کنید. استفاده از یک CTE به شما این امکان را می دهد که table مجازی خود را یک بار تعریف کنید، و بعد چند بار از آن استفاده نمائید.

CTE ها کاربردهای دیگری هم دارند، ولی معمولا صحبت کردن در مورد آنها احتیاج به دانش بیشتری از SQL دارد، و معمولا برای شروع کار، من به کسانی که تازه واردند پیشنهاد می کنم تا یک مدت از derived table برای همچین کارهایی استفاده کنند. تجربه استفاده از و برخورد با مشکلات derived table ها، فهم و قدرت استفاده از CTE ها را خیلی بالا تر می برد.

برای خاطر اطلاع شما، من query بالا را بازنویسی کرده ام. این دفعه، MyFirstSelct به جای اینکه یک derived table باشد، یک CTE است.



with MyFirstSelect as
(
select
o.name,
o.type,
convert( decimal(20,0), o.object_id ) ObjectIDValue
from
sys.objects o
)

select
sum( MyFirstSelect.ObjectIDValue ) SumOfObjectIDValues,
count(1) CountOfObjects
from
MyFirstSelect;