SQL

SQL 历史悠久用户众多，在其模型框架内早已发展到极限，几乎每种简单运算都能找到对应的 SQL 解法，这其中就包括有序运算。

比如比上期的例子：表 stock1001 存储某支股票的交易信息，主要字段有交易日期 transDate、收盘价 price，请计算每个交易日与上个交易日相比收盘价的增长率。

这个例子属于相对位置计算，如果使用窗口函数，SQL 写法相对容易：

select transDate,price,price/lag(price) over(order by transDate)-1 comp from stock1001 

但有些 SQL 不支持窗口函数，实现比上期就会麻烦得多：

With A as(SELECT    t1.transDate,t1.price, COUNT(*) AS rk      
    FROM stock1001 AS t1,           
        stock1001 AS t2       
    WHERE t1.transDate   >t2.transDate or (t1.transDate=t2.transDate and t1.price<=t2.price)      
    GROUP BY  t1.transDate, t1.price      
    ORDER BY rk)
select t1.transDate,(t1.price/t2.price-1) comp  from A as t1 left join  A as t2on t1.rk=t2.rk+1 

上述代码之所以麻烦，首先是因为 SQL 是基于无序集合的，本身没有序号，不方便进行有序运算，为了实现有序运算，就要为无序集合硬造出序号列，这个过程需要自关联和分组汇总，代码比较复杂。其次，比上期属于相对位置计算，如果 SQL 有相对序号，这种计算会简单许多，但 SQL 没有相对序号，只能将上一行关联到本行，变相实现相邻位置计算，代码因此变得复杂。

基于无序集合的 SQL 不方便实现有序运算，窗口函数虽然可以缓解这一状况，但如果运算再复杂时，也依然麻烦。

比如中位数的例子：scores 表存储学生成绩，主要字段有学生编号 studentdid、数学成绩 math，请计算数学成绩的中位数。中位数的定义是：如果记录总数 L 为偶数，则返回中间两个值的均值（序号分别为 L/2 和 L/2+1）；如果 L 为奇数，则返回唯一的中间值（序号为 (L+1)/2）。

SQL 计算中位数的代码：

With A as (select studentdid,math,   row_number() over (order by math) rk from scores),
B as  (select count(1)  L from scores)
select avg(math)  from A where   rk in (                
        select case when   mod(L,2)=1 then   ((L+1) div 2)  else ( L div 2) end no from B                
        union                
        select case when   mod(L,2)=1 then  ((L+1) div 2)  else (L div 2)+1 end  from B)) 

可以看到，虽然已经使用了窗口函数，但 SQL 仍然很复杂。生成序号的过程对于有序集合来说是多余的，但对 SQL 来说就是必不可少的步骤，尤其是本例这种必须显式使用序号的情况，这让代码显得复杂。SQL 实现分支判断也较麻烦，所以对 L 为奇数的情况进行处理时，并没有返回唯一的中间值，而是两个同样的中间值求平均，这个技巧虽然可以简化分支判断，但理解起来稍有困难。

如果使用取整函数，则可以巧妙地跳过判断过程，在简化代码的同时计算出中位数。但这种技巧与中位数的原始定义不同，会造成理解困难，这里没有采用。

再看一个稍复杂的例子：连续上涨天数。库表 AAPL 存储某支股票的股价信息，主要字段有交易日期 transDate、收盘价 price，请计算该股票最长的连续上涨天数。SQL 如下：

select max(continue_inc_days) 
from (select count(*) continue_inc_days        
    from (select sum(inc_de_flag) over(order by transDate) continue_de_days            
    from (select transDate,                      
        case when                          
            price>LAG(price)   over(order by transDate)                     
            then 0 else 1 end inc_de_flag                  
            from AAPL) )
group by continue_de_days) 

按自然思路实现这个任务时，应对日期有序的股票记录进行循环，如果本条记录与上一条记录相比是上涨的，则将连续上涨天数（初始为 0）加 1，如果是下跌的，则将连续上涨天数和当前最大连续上涨天数（初始为 0）相比，选出新的当前最大连续上涨天数，再将连续上涨天数清 0。如此循环直到结束，当前最大连续上涨天数即最终的最大连续上涨天数。

但 SQL 不擅长有序计算，无法用上述自然思路实现，只能用一些难懂的技巧。把按日期有序的股票记录分成若干组，连续上涨的记录分成同一组，也就是说，某天的股价比上一天是上涨的，则和上一天记录分到同一组，如果下跌了，则开始一个新组。最后看所有分组中最大的成员数量，也就是最多连续上涨的天数。

对于这两个稍复杂的有序运算例子，SQL 实现起来就已经很困难了，一旦遇到更复杂的运算，SQL 几乎无法完成。之所以出现这种结果，是因为 SQL 的理论基础就是无序集合，这种天然缺陷无论怎样打补丁，都无法从根本上解决问题。

Python Pandas

Pandas 是 Python 的结构化计算库，常被用作时间序列数据的计算脚本。

作为结构化计算函数库，Pandas 可以轻松实现简单的有序计算。比如，同样计算比上期，Pandas 代码是这样的：

import pandas as pd  
stock1001=pd.read_csv('d:/stock1001.csv')     #return  as a DataFrame
stock1001 ['comp'] = stock1001.math/ stock1001.shift(1).math-1 

上述前两句是为了从文件读取数据，核心代码仅有一句。需要注意的是，Pandas 并不能表示前一行，从而直接实现相对位置计算，但可以用 shift (1) 函数将列整体下移一行，从而变相实现相对位置计算。代码中行和列、前一行和下一行看上去很像，初学者容易混淆。

作为现代程序语言，Pandas 在有序计算方面要比 SQL 先进，主要体现在 Pandas 基于有序集合构建，dataFrame 数据类型天生具有序号，适合进行有序计算。前面那些稍复杂的有序计算，用 SQL 会非常困难，用 Pandas 就相对容易。

同样计算中位数，Pandas 核心代码如下：

…  
df=pd.read_csv('d:/scores.csv')       #return  as a DataFrame  math=df['math']  
L=len(df)  
if L % 2 == 1:      
    result= math[int(L / 2)]  
else:      
    result= (math[int(L / 2 - 1)] +   math[int(L / 2)]) / 2  print(result) |

上述代码中，Pandas 可以直接用 [N] 表示序号，而不用额外制造序号，代码因此得到简化。其次，Pandas 是过程性语言，分支判断比 SQL 易于理解，也不需要技巧来简化代码。

同样稍复杂的例子最长连续上涨天数，Pandas 也比 SQL 容易实现。核心代码如下：

…
aapl = pd.read_sql_query("select price from AAPL order by   transDate", conn)
continue_inc_days=0 ; max_continue_inc_days=0
for i in aapl['price'].shift(0)>aapl['price'].shift(1):            
    continue_inc_days =0 if   i==False else continue_inc_days +1    
    max_continue_inc_days = continue_inc_days   if max_continue_inc_days < continue_inc_days else max_continue_inc_days
print(max_continue_inc_days)
conn.close() 

本例中，Pandas 可以按照自然思路实现，而不必采取难懂的技巧，代码的表达效率要比 SQL 高得多。

有点遗憾的是， 有序计算常常要涉及相对位置计算，但 Pandas 不能直接表达相对位置，只能把列下移一行来变相表示本行的上一行，理解时有点困难。

Pandas 在有序计算方面的确比 SQL 容易些，但遇到更复杂的情况，Pandas 也会变得很繁琐，下面试举两例。

比如过滤累计值的例子：表 sales 存储客户的销售额数据，主要字段有客户 client、销售额 amount，请找出销售额累计占到一半的前 n 个大客户，并按销售额从大到小排序。Pandas 代码如下：

import pandas as pd
sale_info = pd.read_csv("d:/sales.csv")
sale_info.sort_values(by=‘Amount’,inplace=True,ascending=False)
half_amount = sale_info[‘Amount’].sum()/2
vip_list = []
amount = 0
for client_info in sale_info.itertuples():    
    amount += getattr(client_info, ‘Amount’)    
    if amount < half_amount:              
        vip_list.append(getattr(client_info, ‘Client’))    
    else:              
        vip_list.append(getattr(client_info, ‘Client’))        breakprint(vip_list) |

再比如计算股价最高 3 天的涨幅：表 stock1001 存储某支股票的每日股价，主要字段有交易日期 transDate、收盘价 price，请将股价最高的三天按逆序排列，计算每一天相比前一天的涨幅。Pandas 代码如下：

import pandas as pd
stock1001 = pd.read_csv("d:/stock1001_price.txt",sep   = ‘\t’)
CL = stock1001[‘CL’]
CL_psort = CL.argsort()[::-1].iloc[:3].values
CL_psort_shift1 = CL_psort-1
CL_rise = CL[CL_psort].values/CL[CL_psort_shift1].values-1
max_3 = stock1001.loc[CL_psort].reset_index(drop = True)
max_3[‘RISE’] = CL_rise
print(max_3)

这些更复杂的例子也需要用到一些难懂的技巧去实现，不仅难以编写，而且难以读懂，这里就不再详细解释。

esProc

与 Pandas 类似，esProc 也具有丰富的结构化计算函数，与 Pandas 不同的是，esProc 除了基于有序集合并支持序号机制外，还提供了方便的相邻引用机制，以及丰富的位置函数，从而快捷方便地实现有序计算。

对于简单的有序计算，esProc 和其他计算脚本一样，都可以轻松实现。比如同样比上期的 esProc 代码：

上面代码 A1 从 csv 文件取数，A2 是核心代码。esProc 可以用直观易懂的 [-1] 表示相对本行的前一行，这是 Pandas 和 SQL 都没有的功能，也是 esProc 更为专业的表现。

同样计算中位数，esProc 核心代码如下：

上述代码中，esProc 可以直接用 [N] 表示序号，而不用额外制造序号，代码更为简洁。esProc 同样是过程性语法，既可以用 if/else 语句实现大段的分支，也可以像本例一样，用 if 函数实现简洁的判断。

同样稍复杂的例子最长连续上涨天数，esProc 也比 SQL/Pandas 容易实现。核心代码如下：

本例中，esProc 可以按照自然思路实现，而不必采取特殊的技巧，代码表达效率要比 SQL 更高。除此外， esProc 既可以用循环语句实现大段的循环，也可以像本例一样，用循环函数 max 实现简洁的循环聚合。

esProc 是更为专业的结构化计算语言，即使遇到更复杂的有序计算，也能较为轻松地实现。

比如过滤累计值的例子，esProc 只需如下代码：

本例按自然思维实现，先在 A2 计算出从最大的客户到每个客户的累计值，再在 A3 算出最大累计值的一半，在 A4 算出累计值大于 A3 的位置，最后按位置取数据就是所需结果。这里有体现 esProc 专业性的两处特色，其一是 A3 中的 m 函数，该函数可以逆序取数，-1 表示倒数第一条；其二是 A4 中的 pselect，可以按条件返回序号。这两种函数都可以有效简化有序计算。

再比如计算股价最高那 3 天的涨幅，esProc 只需如下代码：

上述代码中，A2 中的 ptop 表示前 N 条的位置，和前面的 pselect 类似，返回的不是记录的集合，而是序号的集合，类似这样的函数在 esProc 中还有很多，其目的都是简化有序计算。A4 中的 #也是 esProc 的特色，直接表示序号字段，使用起来非常方便，不必像 SQL 那样额外制造，或 Pandas 那样设定 index。

经过比较我们可以发现，esProc 具备丰富的结构化函数，是专业的结构化计算语言，可以轻松实现常见的有序计算，即使更复杂的计算也能有效简化，是更加理想的时间序列数据计算脚本。