YibNgernYibTong

Ranking functions in SQL Server 2005

2012-06-21T18:53:00.001-07:00

Ranking function เป็นของเล่นใหม่อีกชิ้นบน MS SQL Server 2005 ครับ

ปกติเวลาเราจะสร้างคำสั่ง Select ที่พ่วงฟิลด์ที่ไว้แสดงลำดับของ Record

หรือการจัดลำดับข้อมูลตามเงื่อนไข บน MS SQL version ก่อนหน้านี้เราต้องทำบน Temp table ใช่ไหมครับ

ซึ่งค่อนข้างเสียเวลาในการ coding มา version 2005 ก็ได้มี feature ใหม่ให้ได้เรียกใช้นั่นก็คือ Ranking function

ซึ่งแต่ละ function อธิบายคร่าว ๆ ได้ดังนี้ครับ

Row_number(): ตัวนี้เบสิคสุด ใช้สำหรับสร้างหมายเลขลำดับเพื่อกำกับแต่ละ record ที่ทำการ select ขึ้นมาแสดง
ตัวอย่าง :

Query: select row_number() over(order by sal desc) rank, empno,ename,sal from emp

Rank(): ใช้สำหรับจัดอันดับตามเงื่อนไข ของฟิลด์ที่กำหนด เช่นการจัดอับดับคะแนนของบุคคลในฝ่าย คะแนนซ้ำก็จะอยู่ในลำดับเดียวกัน

ตัวอย่าง

Query: select rank() over(order by sal desc) rank, empno,sal,ename from emp

Dense_rank() ทำงานเหมือน Rank() แต่จะเรียงลำดับข้อมูลไปเลย เมื่อมีคะแนนซ้ำกันจะไม่เว้นลำดับว่างไว้

ตัวอย่าง

Query: select dense_rank() over(order by sal desc) rank, empno,sal,ename from emp

Ntile(n): เป็นฟังก์ชั่นในการจัดกลุ่มของข้อมูลใน Table ออกเป็นจำนวน Record ที่เท่า ๆ กัน เหมาะสำหรับการแบ่งข้อมูลเป็น Page เพื่อแสดง

ตัวอย่าง

Query: select ntile(2) over(order by sal desc) rank, empno,sal,ename from emp

หวังว่าคงมีประโยชน์สำหรับนักพัฒนาทุก ๆ ท่านสำหรับไปประยุกต์ใช้ในงานต่าง ๆ นะครับ

การสร้าง Partitioning Table บน MS Sql Server 2005

2012-06-21T18:51:00.002-07:00

ว่าด้วยเรื่องการจัดเก็บข้อมูลบนฐานข้อมูล MS-SQL Sever 2005 ในกรณีที่องค์กรของท่าน ๆ ทั้งหลาย มีข้อมูลเป็นจำนวนมากเก็บสุม ๆ ไว้เป็นเวลานาน ไฟล์บน Database ที่แบ่งแค่ Data file กับ Log file ตาม default ของการติดตั้ง โตขึ้น ๆ ทุกวัน จน Database Server เริ่มอืด

MS SQL 2005 มี feature ให้เล่น ซึ่งผมคิดว่ามีประโยชน์มาก ๆ นั่นก็คือการจัดสรรค์ข้อมูลลงไฟล์แบบกระจายโดยอัตโนมัติ หรือเรียกว่าการทำ Partioning ครับ หลักการโดยทั่วไปก็คือ การกระจาย Record ออกในแนวราบ ลง File group หลาย ๆ ไฟล์ ที่เราสร้างไว้รองรับโดยอัตโนมัติ รับรองว่าข้อมูลที่เคยมีมากมายมหาศาล ข้อมูลที่ Access แต่ละทีอืดมากมาย จะเร็วขึ้นทันตาเห็น โดยไม่ต้องทำการเพิ่มทรัพยากรด้านฮาร์ดแวร์

ขั้นตอนและวิธ๊การ ขอยกตัวอย่างจาก Adventureworks แล้วกันนะครับ

– ทำการสร้าง partitioning functions.
– ตัวอย่างนี้จะทำการสร้าง Partition function แยกส่วนของข้อมูลเป็น 2 ช่วงคือ ข้อมูลก่อนและหลังวันที่ ’2005-01-01′

create partition function YearPF(datetime)
as range right for values (’20050101′);

– ต่อมาต้องทำการเพิ่ม file group ลงไปใน database ใครถนัด GUI ก็ใช้ GUI ได้นะครับ case นี้ใช้เป็น Command Line ดังนี้
alter database AdventureWorks add filegroup YearFG1;
alter database AdventureWorks add filegroup YearFG2;

– ต่อมาก็ต้องทำการ add file ลงแต่ละ file group
alter database AdventureWorks add file (name = ‘YearF1′, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF1.ndf’) to filegroup YearFG1;
alter database AdventureWorks add file (name = ‘YearF2′, filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF2.ndf’) to filegroup YearFG2;

– ต่อมาก็ต้องทำการเชื่อม file group เข้ากับ partition function โดยใช้ Partitioning Scheme
create partition scheme YearPS
as partition YearPF to (YearFG1, YearFG2)

– สุดท้ายก็ทำการสร้าง Table โดยอ้างอิงถึง Partition Scheme ที่เราทำการสร้างไว้ ข้อมูลก็จะถูกกระจายโดยอัตโนมัติ
create table PartitionedOrders
(
Id int not null identity(1,1),
DueDate DateTime not null,
) on YearPS(DueDate) –ข้อมูลจะถูกแบ่งลงแต่ละ file group โดยใช้ field ‘DueDate’ เป็นเงื่อนไขในการกระจาย Data

– ทดสอบการ insert ข้อมูล
insert into PartitionedOrders values(’2010-09-01′)
insert into PartitionedOrders values(’1900-02-07′)
insert into PartitionedOrders values(getdate())
insert into PartitionedOrders values(’1998-10-30′)
insert into PartitionedOrders values(’2005-01-01′)

– ทีนี้เรามาตรวจสอบดูว่าข้อมูลแต่ละ Record ไปหล่นใส่ partition ไหนบ้างด้วยคำสั่งนี้เลยครับ
select *, $partition.YearPF(DueDate) from PartitionedOrders

– และสามารถดูจำนวน Partition ที่สร้างไว้ด้วยคำสั่งนี้ครับ
select * from sys.partitions where object_id = object_id(‘PartitionedOrders’)

จากการใช้งานที่ผ่านมาสรุปว่าการ ทำ Partition Table มีประโยชน์มาก ๆ ครับ เหมาะกับฐานข้อมูลที่มี Data เยอะไฟล์บวม Partition Table ช่วยท่านได้ แต่ถ้าท่านมี Storage ที่สามารถแยก แต่ละ Partition ลงฮาร์ดดิสก์คนละก้อนได้ละก็ ประสิทธิภาพของ Database โดยรวมจะเพิ่มขึ้นแบบเห็น ๆ โดยไม่ต้องลงทุนเปลี่ยน Database Server ใหม่ครับ

User Defined Function สำหรับแปลงตัวเลข เป็น อักษรภาษาไทย

2012-06-21T18:50:00.002-07:00

ปกติจะเคยเห็น source code ที่ใช้สำหรับแปลงตัวเลขเป็นตัวอักษรกันเยอะแล้วนะครับ

แต่ที่ออฟฟิศผมใช้หลากหลายภาษากันมาก มีอย่างเดียวที่ใช้เหมือนกันก็คือ Database ที่เป็น MS SQL

ทำอย่างไรจะได้ฟังก์ชั่นกลาง ๆ ที่สามารถเรียกใช้ได้ทุกภาษา ก็ต้องแปลงทุกอย่างให้อยู่ในภาษา SQL ล่ะครับ

ตัวอย่างนี้เป็นตัวอย่างหนึ่งที่มีการเรียกใช้บ่อย ๆ ในการพัฒนาระบบงานทั่วไปคงจะใช้กันบ่อยนะครับ

ปล. source ตัวนี้โมมาจากภาษา php

CREATE FUNCTION dbo.udf_Num2Thai (@Number1 Money)
RETURNS VARCHAR(8000)
AS BEGIN
DECLARE @number Numeric(38 , 0)
DECLARE @decimal INT
DECLARE @loops INT
DECLARE @bigLoops INT
DECLARE @counter INT
DECLARE @bigCount INT
DECLARE @mod INT
DECLARE @numbersTable TABLE (number CHAR(1), word VARCHAR(10))
DECLARE @numbersDigit TABLE (number CHAR(1), word VARCHAR(10))
DECLARE @inputNumber VARCHAR(38)
DECLARE @inputNumber1 VARCHAR(38)
DECLARE @inputDecimal VARCHAR(2)
DECLARE @charNumber CHAR(1)
DECLARE @outputString VARCHAR(8000)
DECLARE @outputString1 VARCHAR(8000)
DECLARE @outputChar VARCHAR(10)
DECLARE @outputChar1 VARCHAR(10)
DECLARE @nextNumber CHAR(1)

IF @number1 = 0 RETURN ‘ศูนย์บาท’

– insert data for the numbers and words
INSERT INTO @NumbersTable
SELECT ‘ ‘, ” UNION ALL SELECT ’0′, ”
UNION ALL SELECT ’1′, ‘หนึ่ง’ UNION ALL SELECT ’2′, ‘สอง’
UNION ALL SELECT ’3′, ‘สาม’ UNION ALL SELECT ’4′, ‘สี่’
UNION ALL SELECT ’5′, ‘ห้า’ UNION ALL SELECT ’6′, ‘หก’
UNION ALL SELECT ’7′, ‘เจ็ด’ UNION ALL SELECT ’8′, ‘แปด’
UNION ALL SELECT ’9′, ‘เก้า’

INSERT INTO @NumbersDigit
SELECT ’1′, ” UNION ALL SELECT ’2′, ‘สิบ’
UNION ALL SELECT ’3′, ‘ร้อย’ UNION ALL SELECT ’4′, ‘พัน’
UNION ALL SELECT ’5′, ‘หมื่น’ UNION ALL SELECT ’6′, ‘แสน’

SET @number = FLOOR(@number1)
SET @decimal = FLOOR((@number1 – FLOOR(@number1))* 100)
SET @inputNumber1 = CONVERT(VARCHAR(38), @number)
SET @inputDecimal = CONVERT(VARCHAR(2), @decimal)
SET @bigLoops = FLOOR(LEN(@inputNumber1) / 6) + 1
SET @mod = LEN(@inputNumber1) % 6
SET @bigCount = 1
SET @outputString = ”

WHILE @bigCount <= @bigLoops BEGIN
IF @bigCount = 1 BEGIN
SET @inputNumber = LEFT(@inputNumber1,@mod)
SET @inputNumber1 = RIGHT(@inputNumber1,LEN(@inputNumber1)-@mod)
END
ELSE BEGIN
SET @inputNumber = LEFT(@inputNumber1,6)
IF @bigCount < @bigLoops
SET @inputNumber1 = RIGHT(@inputNumber1,LEN(@inputNumber1)-6)
END

SET @outputString1 = ”
SET @counter = 1
SET @loops = LEN(@inputNumber)
SET @nextNumber = ”
WHILE 1 <= @loops BEGIN
SET @charNumber = SUBSTRING(@inputNumber,@loops,1)
SET @nextNumber = SUBSTRING(@inputNumber,@loops-1,1)
SELECT @outputChar = word FROM @NumbersTable
WHERE @charNumber = number
SELECT @outputChar1 = word FROM @NumbersDigit
WHERE CONVERT(CHAR(1),@counter) = number
IF @charNumber = ’1′ AND LEN(@inputNumber) > 1 AND @counter = 1 AND @nextNumber > ’0′
SET @outputChar = ‘เอ็ด’
IF @charNumber = ’1′ AND LEN(@inputNumber) >= 2 AND @counter = 2 SET @outputChar = ”
IF @charNumber = ’2′ AND LEN(@inputNumber) >= 2 AND @counter = 2 SET @outputChar = ‘ยี่’
IF @charNumber = ’0′ SET @outputChar1 = ”
SELECT @outputString1 = @outputChar + @outputChar1 + @outputString1,
@counter = @counter + 1,
@loops = @loops – 1
END

IF @bigCount < @bigLoops
IF @outputString1 <> ” SET @outputString = @outputString + @outputString1 + ‘ล้าน’
IF @bigCount >= @bigLoops SET @outputString = @outputString + @outputString1 + ‘บาท’
SET @bigCount = @bigCount + 1
END
– Decimal
IF LEN(@inputDecimal)= 1 SET @inputDecimal = ’0′ + @inputDecimal
SET @inputNumber = @inputDecimal
SET @counter = 1
SET @loops = LEN(@inputNumber)
SET @outputString1 = ”
SET @nextNumber = SUBSTRING(@inputNumber,@loops-1,1)
WHILE 1 <= @loops BEGIN
SET @charNumber = SUBSTRING(@inputNumber,@loops,1)
SELECT @outputChar = word FROM @NumbersTable
WHERE @charNumber = number
SELECT @outputChar1 = word FROM @NumbersDigit
WHERE CONVERT(CHAR(1),@counter) = number
IF @charNumber = ’1′ AND LEN(@inputNumber) > 1 AND @counter = 1 AND @nextNumber > ’0′
SET @outputChar = ‘เอ็ด’
IF @charNumber = ’1′ AND LEN(@inputNumber) >= 2 AND @counter = 2 SET @outputChar = ”
IF @charNumber = ’2′ AND LEN(@inputNumber) >= 2 AND @counter = 2 SET @outputChar = ‘ยี่’
IF @charNumber = ’0′ SET @outputChar1 = ”
SELECT @outputString1 = @outputChar + @outputChar1 + @outputString1,
@counter = @counter + 1,
@loops = @loops – 1
END
IF @inputDecimal = ’00′
SET @outputString = @outPutString + ‘ถ้วน’
ELSE SET @outputString = @outputString + @outputString1 + ‘สตางค์’

RETURN @outputString — return the result
END

Using PIVOT and UNPIVOT สำหรับ MS SQL Server นะครับ

2012-06-21T18:49:00.002-07:00

PIVOT แปลง่าย ๆ คือ Table ที่มีอยู่ จะเอาแต่ละ Row ของมันมาแปลงเป็นColumn ซะ
ตัวอย่าง

SE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

ผลลัพธ์ที่ไ้ด้จะเป็นแบบข้างล่างนี้

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5


ส่วนในทางกลับกัน ถ้าข้อมูลที่มีอยู่เก็บอยู่ในรูปแบบ Column 
แล้ว User ไม่พอใจอยากให้มันแสดงเป็น Row แทนซะงั้น (ความพอดีไม่มีในโลก) 
ไม่เป็นไร ให้ Unpivot ช่วย ตามตัวอย่าง
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO

ผลลัพธ์ที่ได้ ก็จะออกมาตามที่ท่าน User ต้องการครับ

VendorID   Employee   Orders
1      Emp1         4
1      Emp2         3
1      Emp3         5
1      Emp4         4
1      Emp5         4
2      Emp1         4
2      Emp2         1
2      Emp3         5
2      Emp4         5
2      Emp5         5
...