SQL表和数据提取

Question

我以前从未使用过 SQL，但我一直在阅读它。我正在阅读的书中有一个练习可以帮助我入门，我还在查找一个名为 W3School 的网站，这本书告诉我尝试以下操作；

交易具有以下结构 -

• trade_id:主键
• 时间戳:交易时间戳
• 证券:基础证券(在交易中购买或出售)
• quantity:底层数量(正数表示买入，负数表示卖出)
• 价格:本次交易 1 个证券项目的价格

考虑下表

 CREATE TABLE tbProduct
        ([TRADE_ID] varchar(8), [TIMESTAMP] varchar(8), [SECURITY] varchar(8), [QUANTITY] varchar(8), [PRICE] varchar(8))
    ;

    INSERT INTO tbProduct
        ([TRADE_ID], [TIMESTAMP], [SECURITY], [QUANTITY], [PRICE])
    VALUES
        ('TRADE1', '10:01:05', 'BP', '+100', '20'),
        ('TRADE2', '10:01:06', 'BP', '+20', '15'),
        ('TRADE3', '10:10:00', 'BP', '-100', '19'),
        ('TRADE4', '10:10:01', 'BP', '-100', '19')
    ;

书中告诉我编写一个查询来查找 10 秒内发生且价格相差超过 10% 的所有交易。结果还应列出 2 笔交易之间的价格差异百分比。

对于一个以前没有接触过 SQL 的人来说，读到这篇文章真的让我很困惑。他们也向我提供了结果，但我不确定他们是如何得出这个结果的。

预期结果:

First_Trade Second_Trade    PRICE_DIFF
TRADE1      TRADE2          25

我创建了一个fiddle如果这有帮助的话。如果有人可以告诉我如何获得预期的结果，这将有助于我理解书中的练习。

谢谢

Answer 1

这将得到你想要的结果。

;with cast_cte
as
(
  select [TRADE_ID], cast([TIMESTAMP] as datetime) timestamp, [SECURITY], [QUANTITY], cast([PRICE] as float) as price
  from tbProduct
)
select t1.trade_id, t2.trade_id, datediff(ms, t1.timestamp, t2.timestamp) as milliseconds_diff,
((t1.price - t2.price) / t1.price) * 100 as price_diff
from cast_cte t1
inner join cast_cte t2
on datediff(ms, t1.timestamp, t2.timestamp) between 0 and 10000
and t1.trade_id <> t2.trade_id
where ((t1.price - t2.price) / t1.price) * 100 > 10
or ((t1.price - t2.price) / t1.price) * 100 < -10

但是，架构和通用查询参数存在许多问题:

1) 列都是 varchar。这是非常低效的，因为它们都需要转换为适当的数据类型才能获得您想要的结果。使用 datetime、int、float 等(我已根据 @Jeroen-Mostert 的建议使用 CTE 来清理查询)

2) 随着表变大，此查询的性能将开始变得非常差，因为所使用的谓词(10 秒时间戳)未正确建立索引。