主页 / dba / 问题 / 303218
Accepted
user2891462
Asked: 2021-11-29 08:47:31 +0800 CST

对于每个元组，获取非零的第一列的名称

  • 772

我在 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”。
hive hiveql

1 个回答

  1. Best Answer

    有一个“微不足道”的解决方案（如下），它假设问题中的保证成立 - 他们今天可能会这样做，但对于不同的查询，谁知道 - 见下面的讨论？

    下面的所有代码都可以在 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;

    同样的结果 - 见小提琴。

    总而言之，你最好重构你的模式！将列名作为数据的一部分，您将数据和元数据混合在一起，这绝不是一种好习惯！

    • 1

相关问题