對於每個元組，獲取非零的第一列的名稱

我在 Hive 中有一張桌子，看起來像：

| Name | 1990 | 1991 | 1992 | 1993 | 1994 |
| Rex  | 0    | 0    | 1    | 1    | 1    |
| Max  | 0    | 0    | 0    | 0    | 1    |
| Phil | 1    | 1    | 1    | 1    | 1    |

我想為每一行獲取非零的第一列的名稱，例如：

| Name | Column |
| Rex  | 1992   |
| Max  | 1994   |
| Phil | 1990   |

對於每一行，保證：

• 至少有一列帶有“1”；和
• 如果 X 列具有“1”，則對於 Y > X 的每一列，Y 列也將具有“1”。

有一個“微不足道”的解決方案（如下），它假設問題中的保證成立 - 他們今天可能會這樣做，但對於不同的查詢，誰知道 - 見下面的討論？

下面的所有程式碼都可以在 fiddle here上找到。

您的原始表格：

CREATE TABLE test
(
 name TEXT,
 y1990 SMALLINT,
 y1991 SMALLINT,
 y1992 SMALLINT,
 y1993 SMALLINT,
 y1994 SMALLINT
);

您的數據：

INSERT INTO test VALUES
('Rex'   ,0    , 0    , 1    , 1     , 1),
('Max'  , 0    , 0    , 0    , 0     , 1),
('Phil' , 1    , 1    , 1    , 1     , 1);

HiveQL 似乎沒有 PIVOT 功能，因此您只需要使用老式的手動方式即可。Akina 指出有一個“捷徑”：

SELECT 
 name,
 CASE 
   WHEN sc_1990 = 1 THEN 1990 -- the CASE will drop out after first "success"!
   WHEN sc_1991 = 1 THEN 1991 
   WHEN sc_1992 = 1 THEN 1992 
   WHEN sc_1993 = 1 THEN 1993 
   WHEN sc_1994 = 1 THEN 1994
 END AS score
FROM test
ORDER BY score;

結果：

name    m_yr
Phil    1990
Rex     1992
Max     1994

但是，對於更長期的解決方案，以及以下範例：

• 您可能不確定給1定（或任何）欄位中是否存在值，或
• 您可能希望在很長一段時間內進行查詢——比如 1960 年到 2010 年——如果表設計迫使您逐年查詢，那麼您的 SQL 將是相當可怕的。

因此，您最好使用以下方法：

CREATE TABLE toradh  -- both "result" and "match" are SQL keywords! - see https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words, so I used an Irish word!
(
 name TEXT,
 yr   SMALLINT,
 score SMALLINT
);

進而：

INSERT INTO toradh
VALUES
('Rex', 1990, 0),
('Rex', 1991, 0),
...
... snipped for brevity
...

並且，檢查SELECT * FROM toradh;：

結果：

name      yr    score
Rex     1990        0
Rex     1991        0
Rex     1992        1
Rex     1993        1
Rex     1994        1
Max     1990        0
...
... snipped for brevity
...

因此，您的查詢可以這樣寫：

SELECT
 name, MIN(yr) -- , MAX(scor) -- see what happens when you uncomment
FROM toradh
WHERE yr BETWEEN 1990 AND 1994  -- or 1960 AND 2010
AND score = 1
GROUP BY name
ORDER BY MIN(yr);

結果（相同）：

name     min
Phil    1990
Rex     1992
Max     1994

您會發現將來編寫查詢要簡單得多，並且您將能夠以****更簡單的方式提出更複雜的數據問題。

例如，想像一下，您的查詢不是在 1990 年到 1994 年之間，而是在 1960 年到 2010 年之間 - 它會很大- 這樣，它的大小將完全相同 - 只是參數從和到的年份會有所不同！

高瘦而不是矮胖的桌子更好！此外，將來，當您提出此類問題時，能否請您自行設置表格和數據 - 它可以防止代表試圖回答的人重複工作，並且它提供了單一的事實來源 - 幫助我們幫你！ps 歡迎來到 dba.se！

幾乎同樣重要的是，上面的簡化查詢假設數據是預先知道的——即您已經聲明：

• 至少有一列帶有“1”；和
• 如果 X 列具有“1”，則對於 Y > X 的每一列，Y 列也將具有“1”。

但是，除了在最微不足道的情況下，通常不能假設已知值（即使可能相當確定），所以您只需要求助於這樣的查詢：

SELECT name, 1990 AS yr, sc_1990 AS score FROM test t1
UNION ALL
SELECT name, 1991 AS yr, sc_1991 FROM test t1
UNION ALL
SELECT name, 1992 AS yr, sc_1992 FROM test t1
UNION ALL
SELECT name, 1993 AS yr, sc_1993 FROM test t1
UNION ALL
SELECT name, 1994 AS yr, sc_1994 FROM test t1;

結果：

name    yr     score
Rex     1990       0
Max     1990       0
Phil    1990       1
Rex     1991       0
...
... snipped for brevity
...

您的查詢（鑑於沒有數據的先驗知識）將如下所示：

WITH cte AS
(
 SELECT name, 1990 AS yr, sc_1990 AS score FROM test t1
 UNION ALL
 SELECT name, 1991 AS yr, sc_1991 FROM test t1
 UNION ALL
 SELECT name, 1992 AS yr, sc_1992 FROM test t1
 UNION ALL
 SELECT name, 1993 AS yr, sc_1993 FROM test t1
 UNION ALL
 SELECT name, 1994 AS yr, sc_1994 FROM test t1
)
SELECT 
 c.name, MIN(c.yr) AS m_yr
FROM cte c 
WHERE c.score != 0
GROUP BY c.name
ORDER BY m_yr;

同樣的結果 - 見小提琴。

總而言之，你最好重構你的模式！將列名作為數據的一部分，您將數據和元數據混合在一起，這絕不是一種好習慣！

引用自：https://dba.stackexchange.com/questions/303218