mysql - 在 SQL (vertica) 中查找特定行

Question

我基本上有一个表，但我从原始表创建了另一个（子集）表。我将原始表Table 1和子表作为Table 2.

Table_1看起来像这样：

Table_2看起来像这样：

Final_table需要看起来像这样：

现在如您所见，Table_2它是通过从Table_1. 现在我真正需要的是一种方法，首先在 中查找这些完全相同的数据行Table_1，然后使用日期（时间戳）为每一行向后查找，当value2fromTable_2与 text from 匹配时找到一行Table_1。无论何时，获取该值并将其添加到value_original列中。

Final表显示了我需要得到的结果。如何使用 Vertica sql 执行此操作？

我试过这段代码：

SELECT *
FROM
  (SELECT table_2.*, table_1.value as value_original
  FROM
      (Select * from table
      where date < '1/10/2020' 
      and text = 'settle') as table_2
  LEFT JOIN
      (Select * from table where date < '1/10/2020') as table_1
  ON table_2.id1 = table_1.id1 and table_2.id2 = table_1.id2 and table_2.value2 = table_1.text) as final_table
where value_original is not null
ORDER BY date

直到现在我才意识到我选择的别名有多糟糕，对此感到抱歉。在尝试了这段代码之后，我最终遇到了在 Table_1 中多次具有完全相同的值的情况，但我需要使用日期（时间戳）倒退的最接近 Table_2 中选定行的值。我不知道该怎么做。有什么建议么？

谢谢！

标签： mysqlsqljoinvertica

Answer 1

您正在使用 Vertica，因此使用特定于 Vertica 的东西是合法的。

我们正在研究时间序列，在这里，专栏date和time设定节奏。

您将在下面的脚本中看到的两个细节是：

• “会话化”——这就是CONDITIONAL_TRUE_EVENT()OLAP 功能。此函数从返回 0 开始，每次布尔表达式参数为真时返回一个递增 1 的数字，每次 PARTITION BY 表达式更改时重置为 0。我用它将行分组txt为“解决”行之前的所有内容。
• 分析限制子句-LIMIT <n> OVER (PARTITION BY ... ORDER BY ...)可用于获取组内的第一行或最后一行。

因此，我运行全选以获取会话标识符，并将全选命名为全选w_sess，并从中运行全选w_sess以获取会话中的最后一行，该行txt是您提供的 3 个名称之一，并将其​​命名orig_rows为最后通过我在构建时获得的会话标识符加入两者w_sess。为了在脚本中清晰起见，我在注释中显示了两个公用表表达式的内容。

玩的开心...

WITH
input(id1,id2,txt,val,dt,tm) AS (
          SELECT 1,1,'jane'   ,97,DATE'2020-01-01','05:30:22'::TIME
UNION ALL SELECT 1,1,'henry'  ,54,DATE'2020-01-01','06:30:22'::TIME
UNION ALL SELECT 1,1,'jane'   ,10,DATE'2020-01-01','07:30:22'::TIME
UNION ALL SELECT 1,1,'jack'   , 2,DATE'2020-01-01','08:30:22'::TIME
UNION ALL SELECT 1,1,'settle' ,30,DATE'2020-01-01','09:30:22'::TIME
UNION ALL SELECT 1,1,'kara'   ,16,DATE'2020-01-01','10:30:22'::TIME
UNION ALL SELECT 1,1,'sam'    ,46,DATE'2020-07-01','11:30:22'::TIME
UNION ALL SELECT 1,1,'pam'    ,14,DATE'2020-07-01','12:30:22'::TIME
UNION ALL SELECT 1,1,'settle' ,27,DATE'2020-07-01','13:30:22'::TIME
UNION ALL SELECT 1,1,'michael',90,DATE'2020-07-01','14:30:22'::TIME
UNION ALL SELECT 1,1,'tom'    ,10,DATE'2020-07-01','15:30:22'::TIME
UNION ALL SELECT 1,1,'jackson',20,DATE'2020-07-01','16:30:22'::TIME
UNION ALL SELECT 1,1,'settle' ,40,DATE'2020-07-01','17:30:22'::TIME
)
,
w_sess AS (
SELECT
  CONDITIONAL_TRUE_EVENT(LAG(txt)='settle') OVER(
   PARTITION BY id1,id2 ORDER BY dt,tm
  ) AS session_id
, *
FROM input
)
-- select * from w_sess;
-- out  session_id | id1 | id2 |   txt   | val |     dt     |    tm    
-- out ------------+-----+-----+---------+-----+------------+----------
-- out           0 |   1 |   1 | jane    |  97 | 2020-01-01 | 05:30:22
-- out           0 |   1 |   1 | henry   |  54 | 2020-01-01 | 06:30:22
-- out           0 |   1 |   1 | jane    |  10 | 2020-01-01 | 07:30:22
-- out           0 |   1 |   1 | jack    |   2 | 2020-01-01 | 08:30:22
-- out           0 |   1 |   1 | settle  |  30 | 2020-01-01 | 09:30:22
-- out           1 |   1 |   1 | kara    |  16 | 2020-01-01 | 10:30:22
-- out           1 |   1 |   1 | sam     |  46 | 2020-07-01 | 11:30:22
-- out           1 |   1 |   1 | pam     |  14 | 2020-07-01 | 12:30:22
-- out           1 |   1 |   1 | settle  |  27 | 2020-07-01 | 13:30:22
-- out           2 |   1 |   1 | michael |  90 | 2020-07-01 | 14:30:22
-- out           2 |   1 |   1 | tom     |  10 | 2020-07-01 | 15:30:22
-- out           2 |   1 |   1 | jackson |  20 | 2020-07-01 | 16:30:22
-- out           2 |   1 |   1 | settle  |  40 | 2020-07-01 | 17:30:22
-- out (13 rows)
-- out 
-- out Time: First fetch (13 rows): 119.260 ms. All rows formatted: 119.315 ms
,
orig_rows AS (
SELECT
  *
FROM w_sess
WHERE txt IN('jane','pam','tom')
LIMIT 1 OVER(PARTITION BY session_id,txt ORDER BY dt DESC,tm DESC)
)
-- SELECT * FROM orig_rows;
-- out  session_id | id1 | id2 | txt  | val |     dt     |    tm    
-- out ------------+-----+-----+------+-----+------------+----------
-- out           0 |   1 |   1 | jane |  10 | 2020-01-01 | 07:30:22
-- out           1 |   1 |   1 | pam  |  14 | 2020-07-01 | 12:30:22
-- out           2 |   1 |   1 | tom  |  10 | 2020-07-01 | 15:30:22
-- out (3 rows)
-- out 
-- out Time: First fetch (3 rows): 211.165 ms. All rows formatted: 211.222 ms
 SELECT 
  w_sess.id1
, w_sess.id2
, w_sess.txt
, w_sess.val
, w_sess.dt
, orig_rows.txt AS value2
, orig_rows.val AS value_orig
FROM w_sess join orig_rows USING(session_id)
WHERE w_sess.txt='settle'
ORDER BY w_sess.session_id
;
-- out  id1 | id2 |  txt   | val |     dt     | value2 | value_orig 
-- out -----+-----+--------+-----+------------+--------+------------
-- out    1 |   1 | settle |  30 | 2020-01-01 | jane   |         10
-- out    1 |   1 | settle |  27 | 2020-07-01 | pam    |         14
-- out    1 |   1 | settle |  40 | 2020-07-01 | tom    |         10
-- out (3 rows)
-- out 
-- out Time: First fetch (3 rows): 540.519 ms. All rows formatted: 540.566 ms