Oracle/SQL - 返回半唯一记录

发布于 2024-12-05 23:47:01 字数 965 浏览 3 评论 0原文

我想做的是遍历一个表并根据某个字段返回半唯一的结果。例如，对于此数据，

field1  segment     field2  field3  field4  etc
----------------------------------------------- 
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S4          xxx     xxx     xxx     xxx
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S3          xxx     xxx     xxx     xxx

我想要做的是返回每个段类型的 1 条记录。其他数据应来自所选记录，但只要我为每种段类型获得 1 个唯一记录，哪条记录并不重要。

field1  segment     field2  field3  field4  etc
----------------------------------------------- 
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S4          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S3          xxx     xxx     xxx     xxx

希望这是有道理的。此外，这将针对 Oracle 运行，因此无论哪种最有效的方法都可以（源表将有 1000 万条记录和 30 个不同的段）

原文

what I'm trying to do is go through a table and return semi-unique results based on a certain field. So for example with this data

field1  segment     field2  field3  field4  etc
----------------------------------------------- 
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S4          xxx     xxx     xxx     xxx
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S3          xxx     xxx     xxx     xxx

What I want to do is return 1 records of every segment type. The other data should come from the selected record, but which record that is doesn't matter as long as I get 1 unique record for each segment type.

field1  segment     field2  field3  field4  etc
----------------------------------------------- 
xxxx    S1          xxx     xxx     xxx     xxx
xxxx    S4          xxx     xxx     xxx     xxx
xxxx    S2          xxx     xxx     xxx     xxx
xxxx    S3          xxx     xxx     xxx     xxx

Hopefully that makes sense. Also this will run against Oracle so whatever the most efficient way to do this would be great (source table will have something like 10 mil records and 30 distinct segments)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

听，心雨的声音 2024-12-12 23:47:01

您可以执行类似的操作，

SELECT segment,
       field1,
       field2,
       field3,
       field4
  FROM (SELECT segment,
               field1,
               field2,
               field3,
               field4, 
               row_number() over (partition by segment order by rownum) rnk
          FROM table_name)
 WHERE rnk = 1

这将为每个SEGMENT选择任意行。当将来有人决定他们确实关心选择哪一行时，您可以调整分析函数中的 ORDER BY。

You could do something like

SELECT segment,
       field1,
       field2,
       field3,
       field4
  FROM (SELECT segment,
               field1,
               field2,
               field3,
               field4, 
               row_number() over (partition by segment order by rownum) rnk
          FROM table_name)
 WHERE rnk = 1

This will pick an arbitrary row for each SEGMENT. When, in the future, someone decides that they do care what row is picked, you can adjust the ORDER BY in the analytic function.

回复收藏 0 原文

情魔剑神 2024-12-12 23:47:01

例如，我知道当您只想返回段列时，此技术有效。

    SELECT DISTINCT segment
    FROM YOUR_TABLE_NAME

但是，当您尝试这样查询时：

    SELECT DISTINCT SEGMENT, FIELD2, FIELD3
    FROM YOUR_TABLE_NAME
    WHERE BLAH = BLAH

这将为每个段返回不止一行，其中包含不同的信息。

这个问题似乎与发现的问题有点相似这里。

如果我没有正确理解您的问题，请发表评论=）

I know this technique works when you only want to return the segment column for example.

    SELECT DISTINCT segment
    FROM YOUR_TABLE_NAME

But, when you try and query as so:

    SELECT DISTINCT SEGMENT, FIELD2, FIELD3
    FROM YOUR_TABLE_NAME
    WHERE BLAH = BLAH

This will return more than one row for each segment with different info in it.

This problem seems kinda similar to the problem found here.

Please comment if i am not understanding your problem correctly =)

回复收藏 0 原文

日记撕了你也走了 2024-12-12 23:47:01

如果您不想输入所有字段名称：

 SELECT * 
 FROM table_name t0 WHERE NOT EXISTS (
     SELECT * FROM table_name t1
     WHERE t1.segment = t0.segment
     AND t1.ROWNUM < t0.ROWNUM
     ;

我不知道在 oracle 中到底如何调用 tuple-id/rownumber/object-id，但我确信它存在。

If you don't want to type all the field names:

 SELECT * 
 FROM table_name t0 WHERE NOT EXISTS (
     SELECT * FROM table_name t1
     WHERE t1.segment = t0.segment
     AND t1.ROWNUM < t0.ROWNUM
     ;

I don't know exactly how exactly the tuple-id/ rownumber / object-id is called in oracle, but I'm sure it exists.

回复收藏 0 原文

~没有更多了~