问题描述
我的应用包含公用事业仪表用法。我们必须处理的事情之一
是用法明显不正确的时候。也许有人在输入
读数等时写下电表
读数不正确或误差为10倍。其他时候使用率为零或以某种方式输入为一个
的负数。
所以我正在考虑添加搜索此类异常的功能。对于
实例，显示抄表比前12个月的平均值b / b $ b $高25％的月份。或者显示特定仪表的月份，其中
相邻每月使用量相差20％。这是一个数据示例
Meter 5678
Jan-06 100
Feb-06 105
Mar-06 75
06年6月90
06年5月101
Jun-06 900
Jul-06 89
所以你可以从这些数据中看出900显然不正确，可能
应该是90.在06年3月使用75出现在搜索上的地方
是相邻月份之间25％或更多的差异。我们可能还会给b $ b代码搜索零使用和负面使用的功能。
请记住，我们有几千米左右10万美元每月每小时的使用费用超过数年。
我正在寻找实现此功能的方法。通过表格搜索行
可能需要很长时间。是否有一个聪明的方法来通过单独的SQL或主要通过SQL来处理这个问题？或者
有没有人有任何其他建议？看起来这可能是一个非常缓慢的过程。
谢谢。
-
通过AccessMonster.com发布的消息
My app contains utility meter usage. One of the things we have to deal with
is when a usage is clearly incorrect. Perhaps someone wrote the meter
reading down incorrectly or made a factor of 10 error when entering the
reading, etc. At other times the usage is zero or somehow was entered as a
negative number.
So I''m thinking about adding functionality to search for such anomalies. For
instance, show months where the meter reading is 25% higher than the average
for the prior 12 months. Or show months for a particular meter where there
is a difference of 20% between adjacent monthly usage. Here''s a data example
Meter 5678
Jan-06 100
Feb-06 105
Mar-06 75
Apr-06 90
May-06 101
Jun-06 900
Jul-06 89
So you can see from this data that 900 is clearly incorrect and probably
should be 90. The 75 usage in Mar-06 would show up on a search where there
is a difference between adjacent months of 25% or more. We''ll probably also
code the functionality to search for zero usage and negative usage.
Bear in mind that we have several thousand meters and around a 100,000
monthly meter usages spanning several years.
I''m looking for an approach to implement this functionality. Searching row
by row through the tables would probably take a very long time. Is there a
clever way to handle this through SQL alone or mostly through SQL? Or does
anyone have any other suggestions? It would seem that this could be a very
slow process.
Thanks.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1
推荐答案
OTTOMH
SELECT m.Reading，（m.Reading-sq.Average）/sq.StDev AS ZScore FROM米m
LEFT JOIN
[选择平均（Meter.Reading）AS平均值，StDev（Meter.Reading）AS StDev
FROM Meter] 。 sq
ON m.Reading * 1000< sq.Average
WHERE（（m.Reading-sq.Average）/sq.StDev）> = 2
ORDER BY（m.Reading-sq.Average）/sq.StDev
当然，你必须根据自己的情况修改它。我有
建议得分> = 2会怀疑但你自己的经验
将是这里最好的指南。
不，我真的不希望你能用这个，但希望
永恒。
-
Lyle Fairfield
OTTOMH
SELECT m.Reading, (m.Reading-sq.Average)/sq.StDev AS ZScore FROM Meter m
LEFT JOIN
[SELECT Avg(Meter.Reading) AS Average, StDev(Meter.Reading) AS StDev
FROM Meter]. sq
ON m.Reading*1000 <sq.Average
WHERE ((m.Reading-sq.Average)/sq.StDev)>=2
ORDER BY (m.Reading-sq.Average)/sq.StDev
You, of course, would have to modify this for your own situation. I have
suggested that a Score >= 2 would be suspect but your own experience
would be the best guide here.
No, I don''t really expect that you will be able to use this, but hope
springs eternal.
--
Lyle Fairfield
[引用文字剪辑 - 34行]
[quoted text clipped - 34 lines]
OTTOMH
SELECT m.Reading，（m.Reading-sq.Average）/sq.StDev AS ZScore FROM Meter m
LEFT JOIN
[选择平均值（Meter.Reading）AS平均值，StDev（Meter.Reading）AS StDev
FROM Meter]。 sq
ON m.Reading * 1000< sq.Average
WHERE（（m.Reading-sq.Average）/sq.StDev）> = 2
ORDER BY（m.Reading -sq.Average）/sq.StDev
当然，你必须根据自己的情况修改它。我已经建议得分> = 2会怀疑，但你自己的经验
将是这里最好的指南。
不，我真的没想到你将能够使用它，但希望
是永恒的。
OTTOMH
SELECT m.Reading, (m.Reading-sq.Average)/sq.StDev AS ZScore FROM Meter m
LEFT JOIN
[SELECT Avg(Meter.Reading) AS Average, StDev(Meter.Reading) AS StDev
FROM Meter]. sq
ON m.Reading*1000 <sq.Average
WHERE ((m.Reading-sq.Average)/sq.StDev)>=2
ORDER BY (m.Reading-sq.Average)/sq.StDev
You, of course, would have to modify this for your own situation. I have
suggested that a Score >= 2 would be suspect but your own experience
would be the best guide here.
No, I don''t really expect that you will be able to use this, but hope
springs eternal.
-
通过AccessMonster.com发布的消息
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1
这篇关于如何找到异常用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！