文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >SQL需要Microsoft访问来确定分离重复记录的百分位数

问SQL需要Microsoft访问来确定分离重复记录的百分位数
EN

Stack Overflow用户
提问于 2014-12-03 15:49:43
回答 1查看 102关注 0票数 0

我有一个化工厂的数据库，其中包含了在一年时间内采集和分析的所有样本。它包含一个Date (取样日期)、Point (取样日期)、Report (测试化学品)和Values (实验室结果)字段。

Points字段有30个记录，连续重复一年。每个重复对所有记录都有相同的Date条目。

我需要找到第75和95百分位数Values的每一个点字段记录的持续时间的Report是“氨”。第75百分位数意味着75%的数据条目具有百分位数值或更少。

最好的尝试是：

代码语言：javascript
运行
复制
SELECT Point, Max(Values)  
FROM Table  
WHERE Values In(  
SELECT TOP 75 PERCENT Values   
FROM Table  
WHERE Report = "Ammonia"   
ORDER BY Values ASC  
)  
GROUP BY Point;

但是这会带来意想不到的结果，因为给出的结果是所有数据的前75%，而不是每个Point。

最后的输出应该是

代码语言：javascript
运行
复制
Point_1 75th_1
Point_2 75th_2
Point_3 75th_3
...
Point_30 75th_30

应当指出的是，数据不一致，有些天只取样29点或24点，每两或三天取样一次，从来不在模式之内。

示例分隔数据

代码语言：javascript
运行
复制
Date;Point;Report;Values
2013-11-25 00:00:00;"HARTE_ANX1";"Ammonia";10,40
2013-11-25 00:00:00;"HARTE_ANX1";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_ANX1";"COD";106,00
2013-11-25 00:00:00;"HARTE_ANX1";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_ANX1";"MLSS";2856,00
2013-11-25 00:00:00;"HARTE_ANX1";"NOX";0,10
2013-11-25 00:00:00;"HARTE_ANX1";"pH";6,90
2013-11-25 00:00:00;"HARTE_ANX1";"PO4";0,00
2013-11-25 00:00:00;"HARTE_ANX2";"Ammonia";11,20
2013-11-25 00:00:00;"HARTE_ANX2";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_ANX2";"COD";129,00
2013-11-25 00:00:00;"HARTE_ANX2";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_ANX2";"MLSS";2834,00
2013-11-25 00:00:00;"HARTE_ANX2";"NOX";0,30
2013-11-25 00:00:00;"HARTE_ANX2";"pH";6,90
2013-11-25 00:00:00;"HARTE_ANX2";"PO4";0,00
2013-11-25 00:00:00;"HARTE_ANX3";"Ammonia";10,20
2013-11-25 00:00:00;"HARTE_ANX3";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_ANX3";"COD";111,00
2013-11-25 00:00:00;"HARTE_ANX3";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_ANX3";"MLSS";3128,00
2013-11-25 00:00:00;"HARTE_ANX3";"NOX";0,20
2013-11-25 00:00:00;"HARTE_ANX3";"pH";6,90
2013-11-25 00:00:00;"HARTE_ANX3";"PO4";0,00
2013-11-25 00:00:00;"HARTE_ANX4";"Ammonia";10,20
2013-11-25 00:00:00;"HARTE_ANX4";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_ANX4";"COD";121,00
2013-11-25 00:00:00;"HARTE_ANX4";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_ANX4";"MLSS";3358,00
2013-11-25 00:00:00;"HARTE_ANX4";"NOX";0,50
2013-11-25 00:00:00;"HARTE_ANX4";"pH";6,90
2013-11-25 00:00:00;"HARTE_ANX4";"PO4";0,00
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"Ammonia";0,40
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"COD";56,00
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"Conductivity";62,00
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"NOX";4,20
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"pH";6,90
2013-11-25 00:00:00;"HARTE_FINAL_CONT";"PO4";0,10
2013-11-25 00:00:00;"HARTE_FINAL1";"Ammonia";0,00
2013-11-25 00:00:00;"HARTE_FINAL1";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_FINAL1";"COD";48,00
2013-11-25 00:00:00;"HARTE_FINAL1";"Conductivity";57,00
2013-11-25 00:00:00;"HARTE_FINAL1";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_FINAL1";"NOX";4,90
2013-11-25 00:00:00;"HARTE_FINAL1";"pH";7,20
2013-11-25 00:00:00;"HARTE_FINAL1";"PO4";0,00
2013-11-25 00:00:00;"HARTE_FINAL2";"Ammonia";0,70
2013-11-25 00:00:00;"HARTE_FINAL2";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_FINAL2";"COD";40,00
2013-11-25 00:00:00;"HARTE_FINAL2";"Conductivity";57,00
2013-11-25 00:00:00;"HARTE_FINAL2";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_FINAL2";"NOX";6,10
2013-11-25 00:00:00;"HARTE_FINAL2";"pH";7,30
2013-11-25 00:00:00;"HARTE_FINAL2";"PO4";0,00
2013-11-25 00:00:00;"HARTE_FINAL3";"Ammonia";0,00
2013-11-25 00:00:00;"HARTE_FINAL3";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_FINAL3";"COD";87,00
2013-11-25 00:00:00;"HARTE_FINAL3";"Conductivity";57,00
2013-11-25 00:00:00;"HARTE_FINAL3";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_FINAL3";"NOX";3,50
2013-11-25 00:00:00;"HARTE_FINAL3";"pH";7,00
2013-11-25 00:00:00;"HARTE_FINAL3";"PO4";0,20
2013-11-25 00:00:00;"HARTE_FINAL4";"Ammonia";0,10
2013-11-25 00:00:00;"HARTE_FINAL4";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_FINAL4";"COD";59,00
2013-11-25 00:00:00;"HARTE_FINAL4";"Conductivity";55,00
2013-11-25 00:00:00;"HARTE_FINAL4";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_FINAL4";"NOX";4,00
2013-11-25 00:00:00;"HARTE_FINAL4";"pH";7,30
2013-11-25 00:00:00;"HARTE_FINAL4";"PO4";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"Ammonia";0,50
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"COD";56,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"MLSS";3272,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"NOX";2,30
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"pH";6,90
2013-11-25 00:00:00;"HARTE_PRIM_REAC1";"PO4";6,30
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"Ammonia";3,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"COD";55,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"MLSS";2504,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"NOX";1,60
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"pH";6,90
2013-11-25 00:00:00;"HARTE_PRIM_REAC2";"PO4";11,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"Ammonia";0,10
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"COD";60,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"MLSS";2818,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"NOX";3,20
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"pH";6,90
2013-11-25 00:00:00;"HARTE_PRIM_REAC3";"PO4";6,50
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"Ammonia";0,40
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"COD";58,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"MLSS";2792,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"NOX";3,00
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"pH";6,90
2013-11-25 00:00:00;"HARTE_PRIM_REAC4";"PO4";6,10
2013-11-25 00:00:00;"HARTE_PST1";"Ammonia";23,10
2013-11-25 00:00:00;"HARTE_PST1";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PST1";"COD";974,00
2013-11-25 00:00:00;"HARTE_PST1";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PST1";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_PST1";"NOX";0,00
2013-11-25 00:00:00;"HARTE_PST1";"pH";6,90
2013-11-25 00:00:00;"HARTE_PST1";"PO4";5,60
2013-11-25 00:00:00;"HARTE_PST2";"Ammonia";22,00
2013-11-25 00:00:00;"HARTE_PST2";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PST2";"COD";1077,00
2013-11-25 00:00:00;"HARTE_PST2";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PST2";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_PST2";"NOX";0,00
2013-11-25 00:00:00;"HARTE_PST2";"pH";6,90
2013-11-25 00:00:00;"HARTE_PST2";"PO4";5,10
2013-11-25 00:00:00;"HARTE_PST3";"Ammonia";20,90
2013-11-25 00:00:00;"HARTE_PST3";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PST3";"COD";1099,00
2013-11-25 00:00:00;"HARTE_PST3";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PST3";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_PST3";"NOX";0,00
2013-11-25 00:00:00;"HARTE_PST3";"pH";6,90
2013-11-25 00:00:00;"HARTE_PST3";"PO4";5,10
2013-11-25 00:00:00;"HARTE_PST4";"Ammonia";21,60
2013-11-25 00:00:00;"HARTE_PST4";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_PST4";"COD";1070,00
2013-11-25 00:00:00;"HARTE_PST4";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_PST4";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_PST4";"NOX";0,00
2013-11-25 00:00:00;"HARTE_PST4";"pH";6,90
2013-11-25 00:00:00;"HARTE_PST4";"PO4";5,30
2013-11-25 00:00:00;"HARTE_RAW";"Ammonia";19,10
2013-11-25 00:00:00;"HARTE_RAW";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_RAW";"COD";2270,00
2013-11-25 00:00:00;"HARTE_RAW";"Conductivity";63,00
2013-11-25 00:00:00;"HARTE_RAW";"MLSS";0,00
2013-11-25 00:00:00;"HARTE_RAW";"NOX";0,00
2013-11-25 00:00:00;"HARTE_RAW";"pH";6,60
2013-11-25 00:00:00;"HARTE_RAW";"PO4";5,20
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"Ammonia";0,10
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"COD";59,00
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"MLSS";2802,00
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"NOX";2,60
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"pH";6,90
2013-11-25 00:00:00;"HARTE_SEC_REAC1";"PO4";5,70
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"Ammonia";0,10
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"COD";65,00
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"MLSS";2896,00
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"NOX";3,20
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"pH";6,90
2013-11-25 00:00:00;"HARTE_SEC_REAC2";"PO4";5,60
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"Ammonia";0,30
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"COD";64,00
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"MLSS";3412,00
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"NOX";3,70
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"pH";6,90
2013-11-25 00:00:00;"HARTE_SEC_REAC3";"PO4";4,80
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"Ammonia";0,60
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"Chlorides";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"COD";66,00
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"Conductivity";0,00
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"MLSS";3208,00
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"NOX";3,10
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"pH";7,00
2013-11-25 00:00:00;"HARTE_SEC_REAC4";"PO4";5,10
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"Ammonia";2,60
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"Chlorides";0,00
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"COD";69,00
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"Conductivity";74,00
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"MLSS";0,00
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"NOX";3,00
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"pH";7,70
2013-11-26 00:00:00;"HARTE_FINAL_CONT";"PO4";0,50
2013-11-26 00:00:00;"HARTE_FINAL1";"Ammonia";3,90
2013-11-26 00:00:00;"HARTE_FINAL1";"Chlorides";0,00
2013-11-26 00:00:00;"HARTE_FINAL1";"COD";79,00
2013-11-26 00:00:00;"HARTE_FINAL1";"Conductivity";75,00
2013-11-26 00:00:00;"HARTE_FINAL1";"MLSS";0,00
2013-11-26 00:00:00;"HARTE_FINAL1";"NOX";0,00
2013-11-26 00:00:00;"HARTE_FINAL1";"pH";7,60
2013-11-26 00:00:00;"HARTE_FINAL1";"PO4";1,00
2013-11-26 00:00:00;"HARTE_FINAL2";"Ammonia";4,30
2013-11-26 00:00:00;"HARTE_FINAL2";"Chlorides";0,00
2013-11-26 00:00:00;"HARTE_FINAL2";"COD";73,00
2013-11-26 00:00:00;"HARTE_FINAL2";"Conductivity";73,00
2013-11-26 00:00:00;"HARTE_FINAL2";"MLSS";0,00
2013-11-26 00:00:00;"HARTE_FINAL2";"NOX";0,00
2013-11-26 00:00:00;"HARTE_FINAL2";"pH";7,50
2013-11-26 00:00:00;"HARTE_FINAL2";"PO4";0,60
2013-11-26 00:00:00;"HARTE_FINAL4";"Ammonia";0,00
2013-11-26 00:00:00;"HARTE_FINAL4";"Chlorides";0,00
2013-11-26 00:00:00;"HARTE_FINAL4";"COD";65,00
2013-11-26 00:00:00;"HARTE_FINAL4";"Conductivity";70,00
2013-11-26 00:00:00;"HARTE_FINAL4";"MLSS";0,00
2013-11-26 00:00:00;"HARTE_FINAL4";"NOX";7,90
2013-11-26 00:00:00;"HARTE_FINAL4";"pH";7,60
2013-11-26 00:00:00;"HARTE_FINAL4";"PO4";0,00
2013-11-26 00:00:00;"HARTE_RAW";"Ammonia";19,30
2013-11-26 00:00:00;"HARTE_RAW";"Chlorides";0,00
2013-11-26 00:00:00;"HARTE_RAW";"COD";1261,00
2013-11-26 00:00:00;"HARTE_RAW";"Conductivity";81,00
2013-11-26 00:00:00;"HARTE_RAW";"MLSS";0,00
2013-11-26 00:00:00;"HARTE_RAW";"NOX";0,00
2013-11-26 00:00:00;"HARTE_RAW";"pH";6,80
2013-11-26 00:00:00;"HARTE_RAW";"PO4";5,10`
sql
database
ms-access
percentile
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-12-04 12:15:48

通过实施以下措施解决了所需的问题：

代码语言：javascript
运行
复制
SELECT Point, MAX(Values)
FROM Table
WHERE Values IN(
SELECT TOP 75 PERCENT Values
FROM Table dummy
WHERE dummy.Report = "Ammonia"
AND Table.Report=dummy.Report
ORDER BY dummy.Values ASC
)
GROUP BY Point
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/27275948

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档