常用的一些数据统计SQL语句
|
admin
2011年3月3日 20:30
本文热度 3017
|
只有在作统计分析的时候，你才能感觉到数据库的强大，才能感觉到数字游戏的趣味性所在。当然，对于企业而言，则是用于进行决策分析的最好支持之一. 最近刚完工的一个项目中对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
如图：
生成测试数据：
/* 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
有了数据，游戏就开始了.呵呵.一个一个来吧，哈哈.
<1>查询所有数据
Select *
FROM DezaiCn_Sale
<2>查询指定销售商的数据
Select *
FROM DezaiCn_Sale where 店铺名='A'
<3>查询指定销售商某日的数据
Select *
FROM DezaiCn_Sale
Where (店铺名 = 'A') AND (日期 = '2008-12-1')
<4>查询指定销售商某时间段的数据
Select *
FROM DezaiCn_Sale
Where (店铺名 = 'A') AND (日期 BETWEEN '2008-1-1' AND '2008-9-1')
注意：前面的日期要小于后面的日期,这里也可以用>= 与<=来实现同样的结果
<5>查询所有销售商按月统计的销售量
Select 日期, 店铺名, 销售量
FROM DezaiCn_Sale
GROUP BY 日期, 店铺名, 销售量
orDER BY 店铺名
这里其实就是排列了一下数据.与<1>中的结果没有什么多大变化.
<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 日期
上面列了这么多，我觉得大家应该很清楚其中的变化了。无非就是Sum(),Count(),Max()函数及Between的利用 再加Group By 的使用，而以上这些都是很基础又很实用的。上面这些在统计中一般用于进行数据统计时用.如果再增加一个字段列为产品名称或产品分类，也是要利用Group By去实现统计数据功能。
下面这个表基于增加了产品ID字段进行数据统计分析功能,主要是横向的比较了.关键是行转列，列转行的操作，如果是用存储过程，那就是拼字符串了。
<1>指定店铺在某时间段的销售数据(横向)
我这里用存储数据来实现,这里要注意的是拼字符串时单双引号的使用，我在此吃了不少苦头。
Create procedure DezaiCnTestSale_SalerCompareSaleByDate /*代理销售产品比较分析*/
@ProductIDArray varchar(1000), /*产品ID组*/
@begindate datetime, /*比较开始日期*/
@endDate datetime,/*比较结束日期*/
@Shop varchar(20) /*店铺名*/
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 产品ID in ('+@ProductIDArray+') and 店铺名='''+@Shop+''' group by 产品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>指定产品在某时间段的所选销售商的销售量数据比较
在这里我加了一个店铺名ID的字段，因为用数字进行拼字符串比用字符拼要顺畅得多。(个人之见)
/*
作者: Dezai
日期: 2008-12-01
作用:对具体某产品根据所选择的代理商进行销售比较分析
*/
Create procedure DezaiCnTestSale_SalerCompareSaleByDateAndDetailProduct
@ShopArray varchar(1000), /*店铺ID数组*/
@begindate datetime, /*开始时间*/
@endDate datetime,/*结束时间*/
@产品ID varchar(10) /*具体产品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 产品ID='+@产品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
结果：
该文章在 2011/3/3 20:30:43 编辑过
|
|
400 186 1886
