Informix到HiveQL

我有一個為 Informix 撰寫的 SQL 查詢

SELECT cols
FROM table1 t1, outer(table2 t2, table3 t3)
WHERE t1.id = t2.id and t2.type = t3.type

我們在 Hive 上有相同的表和資料，我想將其轉換為 HiveQL

uj5u.com熱心網友回復：

使用 LEFT JOIN 而不是 OUTER

SELECT cols
FROM table1 t1
     LEFT JOIN 
         (select t2.id as join_key, other_cols 
            from table2 t2
                 inner join table3 t3 ON t2.type = t3.type
         ) s ON s.join_key=t1.id

uj5u.com熱心網友回復：

Informix 風格的外連接非常有趣、獨特，而且完全不標準。Informix 對 ANSI (ISO) 標準 SQL 外連接的實作應該完全無聊，并且與其他 DBMS 相同。

這是一個 SQL 腳本，它創建并填充 3 個表并針對這些表執行 5 個不同的查詢。

CREATE TABLE table1
(
    id      SERIAL NOT NULL PRIMARY KEY,
    data    VARCHAR(32) NOT NULL
);

CREATE TABLE table2
(
    id      INTEGER NOT NULL,
    type    INTEGER NOT NULL,
    info    VARCHAR(32) NOT NULL,
    PRIMARY KEY(id, type)
);

CREATE TABLE table3
(
    type    INTEGER NOT NULL PRIMARY KEY,
    name    VARCHAR(32) NOT NULL
);

INSERT INTO table1 VALUES(100, 'Table 1 - ID 100');
INSERT INTO table1 VALUES(101, 'Table 1 - ID 101');
INSERT INTO table1 VALUES(102, 'Table 1 - ID 102');
INSERT INTO table1 VALUES(103, 'Table 1 - ID 103');
INSERT INTO table1 VALUES(104, 'Table 1 - ID 104');

INSERT INTO table2 VALUES(100, 300, 'Table 2 - ID 100, Type 300');
INSERT INTO table2 VALUES(100, 301, 'Table 2 - ID 100, Type 301');
INSERT INTO table2 VALUES(100, 302, 'Table 2 - ID 100, Type 302');
INSERT INTO table2 VALUES(101, 301, 'Table 2 - ID 101, Type 301');
INSERT INTO table2 VALUES(101, 400, 'Table 2 - ID 101, Type 400');
INSERT INTO table2 VALUES(101, 302, 'Table 2 - ID 101, Type 302');
INSERT INTO table2 VALUES(103, 302, 'Table 2 - ID 103, Type 302');
INSERT INTO table2 VALUES(103, 303, 'Table 2 - ID 103, Type 303');
INSERT INTO table2 VALUES(103, 300, 'Table 2 - ID 103, Type 300');
INSERT INTO table2 VALUES(107, 300, 'Table 2 - ID 107, Type 300');
INSERT INTO table2 VALUES(107, 400, 'Table 2 - ID 107, Type 400');

INSERT INTO table3 VALUES(300, 'Table 3 - Type 300');
INSERT INTO table3 VALUES(301, 'Table 3 - Type 301');
INSERT INTO table3 VALUES(302, 'Table 3 - Type 302');
INSERT INTO table3 VALUES(303, 'Table 3 - Type 303');
INSERT INTO table3 VALUES(304, 'Table 3 - Type 304');
INSERT INTO table3 VALUES(305, 'Table 3 - Type 305');

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1, OUTER(table2 t2, table3 t3)
 WHERE t1.id = t2.id AND t2.type = t3.type;

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1, OUTER(table2 t2, OUTER table3 t3)
 WHERE t1.id = t2.id AND t2.type = t3.type;

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.id
  LEFT JOIN table3 t3 ON t2.type = t3.type;

SELECT t1.id as t1_id, t1.data, s.join_key as t2_id, s.t2_type, s.info, s.t3_type, s.name
  FROM table1 t1
  LEFT JOIN 
       (SELECT t2.id AS join_key, t2.info, t3.name, t2.type as t2_type, t3.type as t3_type
          FROM table2 t2
          JOIN table3 t3 ON t2.type = t3.type
       ) s ON s.join_key = t1.id;

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.id
  JOIN table3 t3 ON t2.type = t3.type;

第一個查詢是來自問題的查詢，并指定了選定的列名。每個查詢中的列名串列都相同。

第二個查詢是一個變體，使用額外的 OUTER 關鍵字。它對應于第三個查詢，這是leftjoin創建的第一個答案。第三個查詢是@leftjoin 生成的第一個查詢。第四個查詢是@leftjoin 創建的“當前答案”（嚴格來說，是第三次修訂）。第五個查詢是第四個查詢的變體——它產生不同的答案，所以它不等價。

這些是查詢的輸出。

查詢 1

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1, OUTER(table2 t2, table3 t3)
 WHERE t1.id = t2.id AND t2.type = t3.type;

查詢 2

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1, OUTER(table2 t2, OUTER table3 t3)
 WHERE t1.id = t2.id AND t2.type = t3.type;

查詢 3

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.id
  LEFT JOIN table3 t3 ON t2.type = t3.type;

查詢 4

SELECT t1.id as t1_id, t1.data, s.join_key as t2_id, s.t2_type, s.info, s.t3_type, s.name
  FROM table1 t1
  LEFT JOIN 
       (SELECT t2.id AS join_key, t2.info, t3.name, t2.type as t2_type, t3.type as t3_type
          FROM table2 t2
          JOIN table3 t3 ON t2.type = t3.type
       ) s ON s.join_key = t1.id;

查詢 5

SELECT t1.id as t1_id, t1.data, t2.id as t2_id, t2.type as t2_type, t2.info, t3.type as t3_type, t3.name
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.id
  JOIN table3 t3 ON t2.type = t3.type;

除非我遺漏了什么，否則 Q1 和 Q4 的輸出是相同的（所以@leftjoin 的答案是正確的）；其他查詢的輸出彼此不同（以及 Q1 和 Q4）。

標籤：sql 加入 蜂巢 蜂巢 通知

上一篇：如何在不添加額外行的情況下使用left_joinR？

下一篇：在R中，當ID列存在但不相關時如何應用VLOOKUP？