英文:
Different results are obtained with "order by" used in both queries with and without the filtering condition
问题
我想计算表格M_1中1分钟数据源的特定子集。以下是我的脚本：
one=select code,dates, iif(deltas(J)>0,1,0) as J_up from (select * , MACD(close, 5, 34, 5) as `DIF`DEA`MACD`J from M_1 where code="000001" order by code,dates)
all=select code,dates, iif(deltas(J)>0,1,0) as J_up from (select * , MACD(close, 5, 34, 5) as `DIF`DEA`MACD`J from M_1 order by code,dates)
使用order by子句，我执行了两种类型的查询：一种检索所有数据，另一种按股票ID“000001”过滤数据。但结果不同：
我不明白为什么会这样。有人可以帮助我吗？
英文:
I want to calculate a specific subset of data from the 1-minute data source in table M_1. Here are my scripts:
one=select code,dates, iif(deltas(J)>0,1,0) as J_up from (select * , MACD(close, 5, 34, 5) as `DIF`DEA`MACD`J from M_1 where code="000001" order by code,dates)
all=select code,dates, iif(deltas(J)>0,1,0) as J_up from (select * , MACD(close, 5, 34, 5) as `DIF`DEA`MACD`J from M_1 order by code,dates)
Using the order by clause, I performed two kinds of queries: one retrieves all data and the other filters the data by the stock ID "000001". But the results are different:
I don’t understand why is that. Does anyone can help me?
答案1
得分: 1
问题出在select语句在order by子句之前执行。子查询在数据被按照“code”和“dates”排序之前对数据进行计算。由于MACD函数涉及到依赖序列的计算，如果查询范围不同，行的顺序也会不同，这将影响最终的结果。为了解决这个问题，在执行MACD之前，首先按照“code”和“dates”对M_1进行排序。
英文:
The cause lies in that the select statement is executed before the order by clause. The subqueries perform calculations on the data before it is sorted by “code“ and “dates“. Since the MACD function involves sequence-dependent calculations, the order of the rows differs if the query range is different, which will affect the final results. To fix your problem, first sort the M_1 by “code” and “dates” before executing the MACD.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论