比较单个表中两个连续行中的数据 [英] Comparing data in two consecutive rows from a single table
问题描述
我正在尝试用一种优雅，简单的方法来比较来自同一张桌子的两个
连续值。
例如：
SELECT TOP 2数据值来自myTable ORDER BY时间戳DESC
这给了我两个最新值。我想测试这些值的变化率b / b
。如果最上面一行比它低了50％，那么我会执行一些特殊逻辑。
我有什么选择？我能想到这样做的唯一方法是非常难看。很感谢任何形式的帮助。谢谢！
B.
I''m trying to come up with an elegant, simple way to compare two
consecutive values from the same table.
For instance:
SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESC
That gives me the two latest values. I want to test the rate of
change of these values. If the top row is a 50% increase over the row
below it, I''ll execute some special logic.
What are my options? The only ways I can think of doing this are
pretty ugly. Any help is very much appreciated. Thanks!
B.
推荐答案
>> SELECT TOP 2数据值来自myTable ORDER BY时间戳DESC;
这给了我两个最新的值。我想测试这些值的变化率b / b
。如果顶行比它下面的行增加了50％，我会执行一些特殊的逻辑。 <<
TIMESTAMP是标准SQL中的保留字，与T-SQL相匹配
DATETIME。行在表中没有物理排序，所以那部分
没有任何意义。或者它意味着你仍然有一个
顺序文件的心理模型 - 可能是一个带有单列数据的剪贴板
点和一列时钟印记。
这不是如何在RDBMS中考虑它的。如果您有活动，
则需要显示持续时间。这是爱因斯坦的物理学和
芝诺的悖论。此外，每行必须代表一个完整的
事实本身，而不是事实的一半。让我们再试一次：
创建表
（start_datavalue DECIMAL（8,4）NOT NULL，
end_datavalue DECIMAL （8,4）NOT NULL，
start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL PRIMARY KEY，
end_time TIMESTAMP， - null表示仍然打开
..
）;
在拍摄每个样本时，进行插入和更新（原谅我的
Standard SQL-92）：
BEGIN ATOMIC
插入Foobar
VALUES（ ：datavalue，（SELECT start_time FROM Foobar WHERE end_time IS
NULL），
CURRENT_TIMESTAMP，NULL，..）;
更新Foobar
SET end_time = CURRENT_TIMESTAMP
WHERE end_time IS NULL;
END;
我认为你想要的代码有点复杂。随时间的变化
需要考虑所涉及的时间。一辆车在10秒钟内从0加速到60英里/小时的价格是一件好事，而另一件车需要花费10美元/小时。但不考虑变化率：
SELECT start_datavalue，end_datavalue，start_time，end_time
FROM Foobar
WHERE start_value / end_time > = 0.5000
或者start_value / end_time> = 2.0000）;
这显示了时间段内事物翻倍或减半的行，
你可以添加（end_time-start_time）来获得持续时间，做其他
比率等等。
>> SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESC;
That gives me the two latest values. I want to test the rate of
change of these values. If the top row is a 50% increase over the row
below it, I''ll execute some special logic. <<
TIMESTAMP is a reserved word in Standard SQL, which matches T-SQL
DATETIME. Rows have no physical ordering in a table, so that part
makes no sense. Or it means that you still have a mental model of a
sequential file -- probably a clipboard with a single column for data
points and a single column for a timeclock imprint.
That is not how to think about it in an RDBMS. If you have an event,
then you need to show a duration. This is Einstein''s physics and
Zeno''s paradoxes. Furthermore, each row must represent a complete
fact in itself, not half a fact. Let''s try again:
CREATE TABLE
(start_datavalue DECIMAL(8,4) NOT NULL,
end_datavalue DECIMAL(8,4) NOT NULL,
start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
NOT NULL PRIMARY KEY,
end_time TIMESTAMP, -- null means still open
..
);
As each sample is taken, do an insert and an update (pardon my
Standard SQL-92):
BEGIN ATOMIC
INSERT INTO Foobar
VALUES (:datavalue, (SELECT start_time FROM Foobar WHERE end_time IS
NULL),
CURRENT_TIMESTAMP, NULL, ..);
UPDATE Foobar
SET end_time = CURRENT_TIMESTAMP
WHERE end_time IS NULL;
END;
I think the code you want is a bit more complex. A change over time
needs to consider the time involved. It is one thing for a car to go
from 0 to 60 mph in 10 seconds and quite another for it to take 10
hours. But without considering the rate of change:
SELECT start_datavalue, end_datavalue, start_time, end_time
FROM Foobar
WHERE start_value/end_time >= 0.5000
OR start_value/end_time >= 2.0000);
This shows the rows where things doubled or halved in their timeslots,
you can add (end_time-start_time) to get the duration, do other
ratios, etc.
- CELKO - （jc*******@earthlink.net）写道：
--CELKO-- (jc*******@earthlink.net) writes:
TIMESTAMP是标准SQL中的保留字，它匹配T-SQL
DATETIME。行在表中没有物理排序，因此该部分没有任何意义。或者它意味着你仍然有一个
顺序文件的心理模型 - 可能是一个剪贴片，其中有一列用于数据点和一列用于时钟印记。
TIMESTAMP is a reserved word in Standard SQL, which matches T-SQL
DATETIME. Rows have no physical ordering in a table, so that part
makes no sense. Or it means that you still have a mental model of a
sequential file -- probably a clipboard with a single column for data
points and a single column for a timeclock imprint.
为什么不呢？我敢打赌潜在的商业问题看起来像
那样！
你知道，大多数使用数据库的人都试图解决
现实问题，而不是关系代数练习。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/ books.asp
" - CELKO - " < JC ******* @ earthlink.net>在消息中写道
news：18 ************************** @ posting.google.c om ...
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:18**************************@posting.google.c om...
SELECT TOP 2 datavalues from myTable ORDER BY timestamp DESC;
SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESC;
这给了我两个最新的价值观。我想测试这些值的变化率。如果顶行比它下面的行增加了50％，我会执行一些特殊的逻辑。 <<
TIMESTAMP是标准SQL中的保留字，与T-SQL匹配DATETIME。行在表中没有物理排序，因此该部分没有任何意义。或者它意味着你仍然有一个
顺序文件的心智模型 - 可能是一个剪贴片，其中包含一列用于数据点和一列用于时钟印记。
这不是如何在RDBMS中考虑它。如果你有活动，那么你需要显示一个持续时间。这是爱因斯坦的物理学和哲诺的悖论。此外，每一行本身必须代表一个完整的事实，而不是事实的一半。让我们再试一次：
That gives me the two latest values. I want to test the rate of
change of these values. If the top row is a 50% increase over the row
below it, I''ll execute some special logic. <<
TIMESTAMP is a reserved word in Standard SQL, which matches T-SQL
DATETIME. Rows have no physical ordering in a table, so that part
makes no sense. Or it means that you still have a mental model of a
sequential file -- probably a clipboard with a single column for data
points and a single column for a timeclock imprint.
That is not how to think about it in an RDBMS. If you have an event,
then you need to show a duration. This is Einstein''s physics and
Zeno''s paradoxes. Furthermore, each row must represent a complete
fact in itself, not half a fact. Let''s try again:
我不得不在这里不同意Joe。
例如，我们的一个表记录横幅广告投放的时间。
没有开始和结束有一瞬间。
I have to disagree here Joe.
For example, one of our tables records the time a banner ad is served.
There''s no "begin and end" there''s an instant.
这篇关于比较单个表中两个连续行中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
