基于日期的行、列之间的hiveql差异

wkftcu5l 于 2021-06-25 发布在 Hive

关注(0)|答案(1)|浏览(320)

我有一个表（tïU stocks），上面有这样的数据：

exchanged,stock_symbol,closing_date,closing_price
NSE,TCS,2009-08-09,2200.1
NSE,TCS,2009-08-10,2300.1
NSE,TCS,2009-08-11,12200.1
NSE,TCS,2009-08-12,22300.1
NSE,TCS,2009-09-09,2200.1
NSE,TCS,2009-09-10,2300.1
NSE,TCS,2009-09-11,12200.1
NSE,TCS,2009-09-12,22300.1
NSE,INFY,2009-08-09,2500.34
NSE,INFY,2009-08-10,1500.34
NSE,INFY,2009-08-09,7500.34
NSE,INFY,2009-08-10,14500.34
NSE,INFY,2009-09-09,2500.34
NSE,INFY,2009-09-10,1500.34
NSE,INFY,2009-09-09,7500.34
NSE,INFY,2009-09-10,14500.34
NSE,TCS,2010-08-09,2200.1
NSE,TCS,2010-08-10,2300.1
NSE,TCS,2010-08-11,12200.1
NSE,TCS,2010-08-12,22300.1
NSE,TCS,2010-09-09,2200.1
NSE,TCS,2010-09-10,2300.1
NSE,TCS,2010-09-11,12200.1
NSE,TCS,2010-09-12,22300.1
NSE,INFY,2010-08-09,2500.34
NSE,INFY,2010-08-10,1500.34
NSE,INFY,2010-08-09,7500.34
NSE,INFY,2010-08-10,14500.34
NSE,INFY,2010-09-09,2500.34
NSE,INFY,2010-09-10,1500.34
NSE,INFY,2010-09-09,7500.34
NSE,INFY,2010-09-10,14500.34
...
...

我需要编写一个查询，生成如下报告。交换、股票代号、收盘日期、收盘价、昨天收盘价、昨天价差（昨天价格和今天价格之间的价差），输出如下：

+----------------+-------------------+-------------------+--------------------+------------------------+-----------------------+--+
| exchanged      |     stock_symbol  |     closing_date  |     closing_price  |     yesterday_closing  | diff_yesterday_price  |
+----------------+-------------------+-------------------+--------------------+------------------------+-----------------------+--+
| NSE            | INFY              | 2009-08-09        | 2500.34            | NULL                   | NULL                  |
| NSE            | INFY              | 2009-08-09        | 7500.34            | 2500.34                | -5000                 |
| NSE            | INFY              | 2009-08-10        | 14500.34           | 7500.34                | -7000                 |
| NSE            | INFY              | 2009-08-10        | 1500.34            | 14500.34               | 13000                 |
| NSE            | INFY              | 2009-09-09        | 7500.34            | 1500.34                | -6000                 |
| NSE            | INFY              | 2009-09-09        | 2500.34            | 7500.34                | 5000                  |
| NSE            | INFY              | 2009-09-10        | 14500.34           | 1500.34                | -13000                |
| NSE            | INFY              | 2009-09-10        | 1500.34            | 2500.34                | 1000                  |
| NSE            | INFY              | 2010-08-09        | 7500.34            | 14500.34               | 7000                  |
| NSE            | INFY              | 2010-08-09        | 2500.34            | 7500.34                | 5000                  |
.....
.....

请任何人给我一些线索，以有效地做到这一点。
提前谢谢，
当做。

Hive hiveql

来源：https://stackoverflow.com/questions/61352868/hiveql-difference-between-rows-columns-based-in-dates

1条答案

按热度按时间

g6ll5ycj1#

您可以使用配置单元窗口功能 lag() 解决这个问题。您可以在这里阅读有关配置单元中窗口函数的更多信息。
这是工作中的演示 PostgreSQL ，但相同的查询在 HIVE 也。

select
 exchanged,
 stock_symbol,
 closing_date,
 closing_price,
 yesterday_price,
 (yesterday_price - closing_price) as diff_yesterday_price
from
(
    select
        *,
        lag(closing_price) over (partition by stock_symbol order by closing_date) as yesterday_price
    from stockExchange
) la

order by
    stock_symbol,
    closing_date

赞(0）回复(0）举报 2021-06-26

我来回答

基于日期的行、列之间的hiveql差异

1条答案

相关问题

热门标签

最新问答