&

常用的一些數(shù)據(jù)統(tǒng)計SQL語句

admin

2011年3月3日 20:30 本文熱度 3759

　　只有在作統(tǒng)計分析的時候，你才能感覺到數(shù)據(jù)庫的強(qiáng)大，才能感覺到數(shù)字游戲的趣味性所在。當(dāng)然，對于企業(yè)而言，則是用于進(jìn)行決策分析的最好支持之一. 最近剛完工的一個項目中對SQL的運用，讓我又對SQL的理解與使用加深了一層。很高興與大家分享其中的樂趣.

　　廢話少說，我們先建一個原始表吧。

　　新建表：

Create TABLE [dbo].[DezaiCn_Sale] ( 　　[ID] int IDENTITY(1, 1) NOT NULL, 　　[日期] datetime NOT NULL, 　　[銷售量] int NOT NULL, 　　[店鋪名] char(10) COLLATE Chinese_PRC_CI_AS NOT NULL, 　　CONSTRAINT [PK_Test_Sale] PRIMARY KEY CLUSTERED ([ID]) ) ON [PRIMARY] GO

　　如圖：

常用的一些數(shù)據(jù)統(tǒng)計SQL語句

　　生成測試數(shù)據(jù)：

/* Data for the `dbo.DezaiCn_Sale` table　　(Records 1 - 18) */ Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080101', 2500, N'A') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080101', 2700, N'B') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080201', 3600, N'C') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080201', 5800, N'D') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080301', 2500, N'A') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080301', 2700, N'B') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080401', 3600, N'C') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080401', 5800, N'D') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080301', 2500, N'A') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080101', 2700, N'B') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080501', 3600, N'C') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080701', 4000, N'D') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20081201', 2500, N'A') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080301', 2700, N'B') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20081101', 6500, N'C') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20081001', 5800, N'D') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080601', 3800, N'D') GO Insert INTO [dbo].[DezaiCn_Sale] ([日期], [銷售量], [店鋪名]) VALUES ('20080801', 5400, N'B') GO





　　有了數(shù)據(jù)，游戲就開始了.呵呵.一個一個來吧，哈哈.


　　<1>查詢所有數(shù)據(jù)


Select *
FROM DezaiCn_Sale




　　<2>查詢指定銷售商的數(shù)據(jù)


Select *
FROM DezaiCn_Sale where 店鋪名='A'




　　<3>查詢指定銷售商某日的數(shù)據(jù)


Select *
FROM DezaiCn_Sale
Where (店鋪名 = 'A') AND (日期 = '2008-12-1')




　　<4>查詢指定銷售商某時間段的數(shù)據(jù)


Select *
FROM DezaiCn_Sale
Where (店鋪名 = 'A') AND (日期 BETWEEN '2008-1-1' AND '2008-9-1')


　　注意：前面的日期要小于后面的日期,這里也可以用>= 與<=來實現(xiàn)同樣的結(jié)果






　　<5>查詢所有銷售商按月統(tǒng)計的銷售量


Select 日期, 店鋪名, 銷售量
FROM DezaiCn_Sale
GROUP BY 日期, 店鋪名, 銷售量
orDER BY 店鋪名




　　這里其實就是排列了一下數(shù)據(jù).與<1>中的結(jié)果沒有什么多大變化.


　　<6>查詢各銷售商的銷售總量


Select 店鋪名, SUM(銷售量) AS 銷售總量
FROM DezaiCn_Sale
GROUP BY 店鋪名




　　<6>查詢指定銷售商指定時間段的銷售總量


Select 店鋪名, SUM(銷售量) AS 銷售總量
FROM DezaiCn_Sale
Where (店鋪名 = 'A') AND (日期 BETWEEN '2008-1-1' AND '2008-9-1')
GROUP BY 店鋪名




　　<7>查詢具體時間的總的銷售總量


Select SUM(銷售量) AS 銷售總量, 日期
FROM DezaiCn_Sale
Where (日期 = '2008-3-1')
GROUP BY 日期




　　上面列了這么多，我覺得大家應(yīng)該很清楚其中的變化了。無非就是Sum(),Count(),Max()函數(shù)及Between的利用 再加Group By 的使用，而以上這些都是很基礎(chǔ)又很實用的。上面這些在統(tǒng)計中一般用于進(jìn)行數(shù)據(jù)統(tǒng)計時用.如果再增加一個字段列為產(chǎn)品名稱或產(chǎn)品分類，也是要利用Group By去實現(xiàn)統(tǒng)計數(shù)據(jù)功能。


　　下面這個表基于增加了產(chǎn)品ID字段進(jìn)行數(shù)據(jù)統(tǒng)計分析功能,主要是橫向的比較了.關(guān)鍵是行轉(zhuǎn)列，列轉(zhuǎn)行的操作，如果是用存儲過程，那就是拼字符串了。




　　<1>指定店鋪在某時間段的銷售數(shù)據(jù)(橫向)


　　我這里用存儲數(shù)據(jù)來實現(xiàn),這里要注意的是拼字符串時單雙引號的使用，我在此吃了不少苦頭。


Create procedure　　DezaiCnTestSale_SalerCompareSaleByDate　　/*代理銷售產(chǎn)品比較分析*/

@ProductIDArray varchar(1000), /*產(chǎn)品ID組*/

@begindate datetime, /*比較開始日期*/

@endDate datetime,/*比較結(jié)束日期*/

@Shop varchar(20) /*店鋪名*/

as

declare @sql varchar(8000)

set @sql = 'select 產(chǎn)品ID ' 

select @sql = @sql + ' , max(case Convert(Varchar(10),日期,120) when ''' + Convert(Varchar(10),日期,120) + ''' then 銷售量 else 0 end) [' + Convert(Varchar(10),日期,120) + ']' 

from (select distinct 日期 from DezaiCn_Sale where 日期 between @begindate　　and @endDate　　) as a 

set @sql = @sql + ' from DezaiCn_Sale where　　 產(chǎn)品ID in ('+@ProductIDArray+') and 店鋪名='''+@Shop+'''　　group by 產(chǎn)品ID'

exec(@sql)
GO


　　運行效果：


DECLARE @RC int
DECLARE @ProductIDArray varchar(1000)
DECLARE @begindate datetime
DECLARE @endDate datetime
DECLARE @Shop varchar(20)
Select @ProductIDArray = '1,3,4'
Select @begindate = '2008-1-1'
Select @endDate = '2008-9-1'
Select @Shop = 'A'
EXEC @RC = [TEST].[dbo].[DezaiCnTestSale_SalerCompareSaleByDate] @ProductIDArray, @begindate, @endDate, @Shop
DECLARE @PrnLine nvarchar(4000)
PRINT '存儲過程: TEST.dbo.DezaiCnTestSale_SalerCompareSaleByDate'
Select @PrnLine = '　　　　返回代碼 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine


　　效果：




　　<2>指定產(chǎn)品在某時間段的所選銷售商的銷售量數(shù)據(jù)比較


　　在這里我加了一個店鋪名ID的字段，因為用數(shù)字進(jìn)行拼字符串比用字符拼要順暢得多。(個人之見)


/*
作者: Dezai
日期: 2008-12-01
作用:對具體某產(chǎn)品根據(jù)所選擇的代理商進(jìn)行銷售比較分析
*/

Create procedure DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct

@ShopArray varchar(1000), /*店鋪ID數(shù)組*/

@begindate datetime, /*開始時間*/

@endDate datetime,/*結(jié)束時間*/

@產(chǎn)品ID varchar(10) /*具體產(chǎn)品ID*/

as

declare @sql varchar(8000)

set @sql = 'select 店鋪ID ' 

select @sql = @sql + ' , max(case Convert(Varchar(10),日期,120) when ''' + Convert(Varchar(10),日期,120) + ''' then 銷售量 else 0 end) [' + Convert(Varchar(10),日期,120) + ']' 

from (select distinct 日期 from DezaiCn_Sale where 日期 between @begindate　　and @endDate　　) as a 

set @sql = @sql + ' from DezaiCn_Sale where 產(chǎn)品ID='+@產(chǎn)品ID+' and　　店鋪ID in ('+@ShopArray+') group by 店鋪ID'

exec(@sql)
GO




　　效果:


DECLARE @RC int
DECLARE @ShopArray varchar(1000)
DECLARE @begindate datetime
DECLARE @endDate datetime
DECLARE @product_ID varchar(10)
Select @ShopArray = '1,3'
Select @begindate = '2008-1-1'
Select @endDate = '2008-12-1'
Select @product_ID = '3'
EXEC @RC = [TEST].[dbo].[DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct] @ShopArray, @begindate, @endDate, @product_ID
DECLARE @PrnLine nvarchar(4000)
PRINT '存儲過程: TEST.dbo.DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct'
Select @PrnLine = '　　　　返回代碼 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine


　　結(jié)果：