表tablea字段
a1 values count date names
直接1 23 45 2009-08-07 急先锋
直接2 24 46 2009-08-06 急先锋
直接1 26 46 2009-08-06 急先锋
直接1 25 46 2009-08-06 急先锋
直接2 24 46 2009-08-06 紧先锋
直接2 25 46 2009-08-06 紧先锋
直接2 26 46 2009-08-06 紧先锋
直接1 24 46 2009-08-07 急先锋
直接1 27 46 2009-08-07 急先锋要按a1、date、names 分组,求出values的中位值!
a1 values count date names
直接1 23 45 2009-08-07 急先锋
直接2 24 46 2009-08-06 急先锋
直接1 26 46 2009-08-06 急先锋
直接1 25 46 2009-08-06 急先锋
直接2 24 46 2009-08-06 紧先锋
直接2 25 46 2009-08-06 紧先锋
直接2 26 46 2009-08-06 紧先锋
直接1 24 46 2009-08-07 急先锋
直接1 27 46 2009-08-07 急先锋要按a1、date、names 分组,求出values的中位值!
解决方案 »
- 如何给一个动态创建的db增加用户权限
- 使用exec xp_makecab 'f:\t.cab',mszip, 1,'f:\lll\teacher.bak'后,我想解压?
- 新手触发器问题
- 请教SQL多表更新问题
- 求一update语句
- sql server中如何把表中一列名由a改成b,非关键字的
- 想了半个小时也解决不了,请高手帮忙!
- 两个导数据问题(CSV文件,记录特别多)急!!
- 求助:Microsoft OLE DB Provider for SQL Server error '80040e4d'
- 知道ORACLE数据库的用户名和密码,怎么在SQL中访问啊?用OPENROWSET怎么不对啊?
- SQL:将 varchar 值 '2002-2003 ' 转换为数据类型为 int 的列时发生语法错误。
- 怎样把字符数组存到数据库啊
insert into tablea select '直接1',23,45,'2009-08-07','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','急先锋'
insert into tablea select '直接1',26,46,'2009-08-06','急先锋'
insert into tablea select '直接1',25,46,'2009-08-06','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',25,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',26,46,'2009-08-06','紧先锋'
insert into tablea select '直接1',24,46,'2009-08-07','急先锋'
insert into tablea select '直接1',27,46,'2009-08-07','急先锋'
go
select avg([values]) from tablea
/*
24(1 行受影响)
*/
go
drop table tablea
insert into tablea select '直接1',23,45,'2009-08-07','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','急先锋'
insert into tablea select '直接1',26,46,'2009-08-06','急先锋'
insert into tablea select '直接1',25,46,'2009-08-06','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',25,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',26,46,'2009-08-06','紧先锋'
insert into tablea select '直接1',24,46,'2009-08-07','急先锋'
insert into tablea select '直接1',27,46,'2009-08-07','急先锋'
gowith cte as
(
select ROW_NUMBER() over(PARTITION by a1,names order by [date]) no,* from tablea
)
select * from cte a
where no=(select AVG(no) from cte where a1=a.a1 and names=a.names group by a1,names)/*
no a1 values count date names
-------------------- ---------- ----------- ----------- ----------------------- ----------
3 直接1 23 45 2009-08-07 00:00:00.000 急先锋
1 直接2 24 46 2009-08-06 00:00:00.000 急先锋
2 直接2 25 46 2009-08-06 00:00:00.000 紧先锋
(select [values]=max([values],row_number=row_number over (order by max([values]))
from tablea group by a1,date,names) a
wehere a.row_number=(select max(row_number)/2+1 from a )
create table tablea(a1 nvarchar(10),[values] int,[count] int,[date] datetime,names nvarchar(10))
set nocount on
insert into tablea select '直接1',23,45,'2009-08-07','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','急先锋'
insert into tablea select '直接1',26,46,'2009-08-06','急先锋'
insert into tablea select '直接1',25,46,'2009-08-06','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',25,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',26,46,'2009-08-06','紧先锋'
insert into tablea select '直接1',24,46,'2009-08-07','急先锋'
insert into tablea select '直接1',27,46,'2009-08-07','急先锋'
set nocount off
gowith ct1 as
(
select *,px = row_number() over (partition by a1,date,names order by getdate())
from tablea
),ct2 as
(
select a1,date,names,count(*) as cnt
from tablea
group by a1,date,names
)select t.*,e.cnt
from ct1 t join ct2 e on t.a1 = e.a1 and t.date = e.date and t.names = e.names
where charindex(','+ltrim(px)+',',
','+(case when e.cnt%2 = 0 then ltrim((e.cnt/2 + 1))+','+ltrim((e.cnt/2)) else ltrim(e.cnt/2 + 1) end)+',') > 0drop table tablea
/*
a1 values count date names px cnt
---------- ----------- ----------- ----------------------- ---------- -------------------- -----------
直接1 26 46 2009-08-06 00:00:00.000 急先锋 1 2
直接1 25 46 2009-08-06 00:00:00.000 急先锋 2 2
直接1 24 46 2009-08-07 00:00:00.000 急先锋 2 3
直接2 24 46 2009-08-06 00:00:00.000 急先锋 1 1
直接2 25 46 2009-08-06 00:00:00.000 紧先锋 2 3(5 行受影响)
*/
--借用#7演示数据
with cte as
(
select *,id = row_number() over (partition by a1,date,names order by getdate()),
cn=count(*) over (partition by a1,date,names)
from tablea
)
select * from cte where id=ceiling(cn*1.0/2)/*
a1 values count date names id cn
---------- ----------- ----------- ----------------------- ---------- -------------------- -----------
直接1 26 46 2009-08-06 00:00:00.000 急先锋 1 2
直接1 24 46 2009-08-07 00:00:00.000 急先锋 2 3
直接2 24 46 2009-08-06 00:00:00.000 急先锋 1 1
直接2 25 46 2009-08-06 00:00:00.000 紧先锋 2 3(4 行受影响)
*/
--按你说的列分组,当总数为奇数中间值有一个,偶数中间值有俩。create table tablea(a1 nvarchar(10),[values] int,[count] int,[date] datetime,names nvarchar(10))
set nocount on
insert into tablea select '直接1',23,45,'2009-08-07','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','急先锋'
insert into tablea select '直接1',26,46,'2009-08-06','急先锋'
insert into tablea select '直接1',25,46,'2009-08-06','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',25,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',26,46,'2009-08-06','紧先锋'
insert into tablea select '直接1',24,46,'2009-08-07','急先锋'
insert into tablea select '直接1',27,46,'2009-08-07','急先锋'
set nocount off
gowith ct1 as
(
select *,px = row_number() over (partition by a1,date,names order by getdate()),
cnt = count(*) over (partition by a1,date,names)
from tablea
)/*
select * from ct1a1 values count date names px cnt
---------- ----------- ----------- ----------------------- ---------- -------------------- -----------
直接1 26 46 2009-08-06 00:00:00.000 急先锋 1 2
直接1 25 46 2009-08-06 00:00:00.000 急先锋 2 2
直接1 23 45 2009-08-07 00:00:00.000 急先锋 1 3
直接1 24 46 2009-08-07 00:00:00.000 急先锋 2 3
直接1 27 46 2009-08-07 00:00:00.000 急先锋 3 3
直接2 24 46 2009-08-06 00:00:00.000 急先锋 1 1
直接2 24 46 2009-08-06 00:00:00.000 紧先锋 1 3
直接2 25 46 2009-08-06 00:00:00.000 紧先锋 2 3
直接2 26 46 2009-08-06 00:00:00.000 紧先锋 3 3(9 行受影响)*/select *
from ct1
where charindex(','+ltrim(px)+',',
','+(case when cnt%2 = 0 then ltrim((cnt/2 + 1))+','+ltrim((cnt/2)) else ltrim(cnt/2 + 1) end)+',') > 0drop table tablea
a1 values count date names px cnt
---------- ----------- ----------- ----------------------- ---------- -------------------- -----------
直接1 26 46 2009-08-06 00:00:00.000 急先锋 1 2
直接1 25 46 2009-08-06 00:00:00.000 急先锋 2 2
直接1 24 46 2009-08-07 00:00:00.000 急先锋 2 3
直接2 24 46 2009-08-06 00:00:00.000 急先锋 1 1
直接2 25 46 2009-08-06 00:00:00.000 紧先锋 2 3(5 行受影响)--上边最后一个查询的结果集!
因为我只查一个的中间值!
数据量比较大!查出来后数据还是有点误12.85 2011-06-08 中班 2 aaa OP1300 13.918333 15.85 12.85 12.850000 12.850000 3 6
15.85 2011-06-08 中班 2 aaa OP1300 13.918333 15.85 12.85 12.850000 12.850000 4 6
用的你这个方法做的,这个结果是相同的!
有两个值
with ct1 as
(
select distinct *,px = row_number() over (partition by a1,date,names order by getdate()),
cnt = count(*) over (partition by a1,date,names)
from tablea
)
13.85 2011-06-08 中班 10K-1 孔繁强 OP1300 3 5
13.53 2011-05-08 中班 L1 陆杰 OP20 4 7
16.85 2011-04-08 中班 L1 孔繁强 OP30 2 4
17.23 2011-04-08 中班 L1 孔繁强 OP30 3 4
16.53 2011-05-08 中班 L1 孔繁强 OP30 3 6
16.54 2011-05-08 中班 L1 孔繁强 OP30 4 6
13.26 2011-06-08 中班 L1 孔繁强 OP30 4 8
13.53 2011-06-08 中班 L1 孔繁强 OP30 5 8
group by 去重复后还是有这么多重复的数据!
你把with cte 里的所有date,换成convert(varchar(10),date,120)with ct1 as
(
select *,px = row_number() over (partition by a1,convert(varchar(10),date,120),names order by getdate()),
cnt = count(*) over (partition by a1,convert(varchar(10),date,120),names)
from tablea
)
insert into tablea select '直接1',23,45,'2009-08-07','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','急先锋'
insert into tablea select '直接1',26,46,'2009-08-06','急先锋'
insert into tablea select '直接1',25,46,'2009-08-06','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',25,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',26,46,'2009-08-06','紧先锋'
insert into tablea select '直接1',24,46,'2009-08-07','急先锋'
insert into tablea select '直接1',27,46,'2009-08-07','急先锋'
go
;with c1 as(
select distinct a1,[date],names,[values] from tablea
),c2 as(
select a1,[date],names,avg([values]*1.0)vavg from c1 group by a1,[date],names
)
select a.a1,a.[date],a.names,a.[values],b.vavg from c1 a inner join c2 b on a.a1=b.a1 and a.names=b.names and a.date=b.date
where not exists(select 1 from c1 where a1=a.a1 and names=a.names and date=a.date and abs([values]-b.vavg)>abs(a.[values]-b.vavg+0.000001))
/*
a1 date names values vavg
---------- ----------------------- ---------- ----------- ---------------------------------------
直接1 2009-08-06 00:00:00.000 急先锋 26 25.500000
直接1 2009-08-07 00:00:00.000 急先锋 27 24.666666
直接2 2009-08-06 00:00:00.000 急先锋 24 24.000000
直接2 2009-08-06 00:00:00.000 紧先锋 26 25.000000(4 行受影响)*/
go
drop table tablea
2、中位数的优缺点:中位数是样本数据所占频率的等分线,它不受少数几个极端值得影响,有时用它代表全体数据的一般水平更合适。
3、在频率分布直方图中,中位数左边和右边的直方图的面积应该相等,由此可以估计中位数的值。
4、中位数也可表述为第50百分位数,二者等价。
5、直观印象描述:一半比“我”小,一半比“我”大。 中位数的算法
求中位数时,首先要先进行数据的排序(从小到大),然后计算中位数的序号,分数据为奇数个与偶数个两种来求.
中位数算出来可避免极端数据,代表着数据总体的中等情况。
如果总数个数是奇数的话,按从小到大的顺序,取中间的那个数
如果总数个数是偶数个的话,按从小到大的顺序,取中间那两个数的平均数
-- 测试表
CREATE TABLE test_median (
Name varchar(10),
val INT
);
GO-- 测试数据.
INSERT INTO test_median
SELECT 'A', 1000 UNION ALL
SELECT 'A', 2000 UNION ALL
SELECT 'A', 3000 UNION ALL
SELECT 'A', 4000 UNION ALL
SELECT 'A', 5000 UNION ALL
SELECT 'B', 100 UNION ALL
SELECT 'B', 200 UNION ALL
SELECT 'B', 300 UNION ALL
SELECT 'B', 400 UNION ALL
SELECT 'B', 7000 UNION ALL
SELECT 'B', 10000
GOName 为 A 的数据,有5条
Name 为 B 的数据,有6条
直接使用 SQL 语句来进行计算的处理使用2个子查询来计算
1个子查询用来排序
1个子查询用于计算总数
然后根据总数的 奇/偶,来决定哪些行需要进行计算。
SELECT
data_with_rownumber.Name,
AVG(data_with_rownumber.val) AS median
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY val) AS seq,
Name,
val
FROM
test_median
) data_with_rownumber JOIN
(
SELECT
Name, COUNT(1) AS NumOfVal
FROM
test_median
GROUP BY
Name
) data_count
ON (
data_count.Name = data_with_rownumber.Name
AND (
(data_count.NumOfVal % 2 = 0 AND data_with_rownumber.seq IN (data_count.NumOfVal / 2, (data_count.NumOfVal / 2) + 1))
OR
(data_count.NumOfVal % 2 = 1 AND data_with_rownumber.seq = 1 + data_count.NumOfVal / 2)
)
)
GROUP BY
data_with_rownumber.NameName median
---------- -----------
A 3000
B 350(2 行受影响) 使用 Visual C# SQL CLR 创建的聚合函数来处理 使用 VS2010,创建一个
Visual C# SQL CLR 数据库项目 命名为 MyMidNumber
在项目中添加一个 [聚合] 的类代码如下:using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, // 使用 UserDefined 序列化格式
IsInvariantToNulls = true, // 指示聚合是否与空值无关。
IsInvariantToDuplicates = false, // 指示聚合是否与重复值无关。
IsInvariantToOrder = false, // 指示聚合是否与顺序无关。
MaxByteSize = 8000) // 聚合实例的最大大小(以字节为单位)。
]
public struct Median : Microsoft.SqlServer.Server.IBinarySerialize
{
public void Init()
{
// 初始化.
dataList = new List<Decimal>();
} public void Accumulate(SqlDecimal Value)
{
// 新增一个数据
dataList.Add(Value.Value);
} public void Merge(Median Group)
{
// 新增一组数据.
dataList.AddRange(Group.dataList);
} public SqlDecimal Terminate()
{
// 首先排序.
dataList.Sort(); decimal middleVal = 0; if (dataList.Count > 0)
{
if (dataList.Count % 2 == 1)
{
// 当数量为奇数的时候.
// 中位数为中间的那个数字.
// 例如
// 1个数字, 中位为第1个
// 3个数字, 中位为第2个
// 5个数字, 中位为第3个
// 7个数字, 中位为第4个
middleVal = dataList[dataList.Count / 2];
}
else
{
// 当数量为偶数的时候.
// 中位数为 中间2个数的 算数平均
// 例如
// 2个数字, 中位为 (第1个 + 第2个) / 2
// 4个数字, 中位为 (第2个 + 第3个) / 2
// 6个数字, 中位为 (第3个 + 第4个) / 2
// 8个数字, 中位为 (第4个 + 第5个) / 2
middleVal =
(dataList[dataList.Count / 2 - 1]
+ dataList[dataList.Count / 2]) / 2;
}
} return new SqlDecimal(middleVal);
} // 这是分组的所有数据.
private List<Decimal> dataList;
/// <summary>
/// 使用 UserDefined 序列化格式
/// 通过 IBinarySerialize.Read 方法完全控制二进制格式。
/// 从用户定义类型 (UDT) 或用户定义聚合的二进制格式生成用户定义的类型或用户定义的聚合。
/// </summary>
/// <param name="r"></param>
public void Read(System.IO.BinaryReader r)
{
// 初始化数据.
dataList = new List<decimal>();
// 先读取总数量.
int size = r.ReadInt32();
// 依次读取数据,加入列表.
for (int i = 0; i < size; i++)
{
dataList.Add(r.ReadDecimal());
}
} /// <summary>
/// 使用 UserDefined 序列化格式
/// 通过 IBinarySerialize.Read 方法完全控制二进制格式。
/// 将用户定义的类型 (UDT) 或用户定义的聚合转换为其二进制格式,以便保留。
/// </summary>
/// <param name="w"></param>
public void Write(System.IO.BinaryWriter w)
{
// 先写入一个 总数量
w.Write(dataList.Count);
// 依次写入每一个数据.
foreach (Decimal data in dataList)
{
w.Write(data);
}
}
} C# 项目需要记得设置 目标框架为 .NET Framework 3.5
如果使用默认的 .NET Framework 4,可能无法成功的把编译好的 DLL 文件发布到 SQL Server 2008 上面去。在把编辑好的 DLL 文件,加入到数据库的程序集之后。
再执行下面的 SQL 语句。
CREATE AGGREGATE [dbo].[Median](@Value NUMERIC (18))
RETURNS NUMERIC (18)
EXTERNAL NAME [MyMidNumber].[Median]; 最后直接在 SQL 语句里面,使用新编写的聚合函数:SELECT
ISNULL(Name, '全部') AS 名称,
SUM(val) AS 合计,
AVG(val) AS 平均数,
dbo.Median(val) AS 中位数
FROM
test_median
GROUP BY
Name
WITH ROLLUP
名称 合计 平均数 中位数
---------- ----------- ----------- --------------------
A 15000 3000 3000
B 18000 3000 350
全部 33000 3000 2000(3 行受影响)
create table tablea(a1 nvarchar(10),[values] int,[count] int,[date] datetime,names nvarchar(10))
insert into tablea select '直接1',23,45,'2009-08-07','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','急先锋'
insert into tablea select '直接1',26,46,'2009-08-06','急先锋'
insert into tablea select '直接1',25,46,'2009-08-06','急先锋'
insert into tablea select '直接2',24,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',25,46,'2009-08-06','紧先锋'
insert into tablea select '直接2',26,46,'2009-08-06','紧先锋'
insert into tablea select '直接1',24,46,'2009-08-07','急先锋'
insert into tablea select '直接1',27,46,'2009-08-07','急先锋'
go
;with c1 as(
select distinct a1,[date],names,[values] from tablea
),c2 as(
select a1,[date],names,(max([values])*1.0+min([values]))/2 vavg from c1 group by a1,[date],names
)
select a.a1,a.[date],a.names,a.[values],b.vavg from c1 a inner join c2 b on a.a1=b.a1 and a.names=b.names and a.date=b.date
where not exists(select 1 from c1 where a1=a.a1 and names=a.names and date=a.date and abs([values]-b.vavg)>abs(a.[values]-b.vavg+0.000001))
/*
a1 date names values vavg
---------- ----------------------- ---------- ----------- ---------------------------------------
直接1 2009-08-06 00:00:00.000 急先锋 26 25.500000
直接1 2009-08-07 00:00:00.000 急先锋 27 25.000000
直接2 2009-08-06 00:00:00.000 急先锋 24 24.000000
直接2 2009-08-06 00:00:00.000 紧先锋 26 25.000000(4 行受影响)
*/
go
drop table tablea