文章/答案/技术大牛

发布

社区首页 >问答首页 >类似于排序- SQL Oracle的枢轴

问类似于排序- SQL Oracle的枢轴
EN

Stack Overflow用户

提问于 2018-10-12 08:03:00

回答 2查看 54关注 0票数 0

我有一张桌子(书)是这样的：

user_rent | book_rent | rent_from  | rent_to
-----------------------------------------------
Alan Doe  | Macbeth   | 01.07.2018 | 15.07.2018
Alan Doe  | Hamlet    | 16.07.2018 | 01.08.2018
Alan Doe  | Othello   | 02.08.2018 | 31.08.2018
Alan Doe  | King Lear | 01.09.2018 |    
Alex Doe  | Dracula   | 01.07.2018 | 15.07.2018
Alex Doe  | Hamlet    | 16.07.2018 | 01.08.2018
Alex Doe  | Hobbit    | 02.08.2018 | 31.08.2018
Alex Doe  | Inferno   | 01.09.2018 |    
Anna Doe  | 1984      | 01.07.2018 | 15.07.2018
Anna Doe  | King Lear | 16.07.2018 | 01.08.2018
Anna Doe  | Hobbit    | 02.08.2018 | 31.08.2018
Anna Doe  | Dracula   | 01.09.2018 |    
Ella Doe  | Macbeth   | 01.07.2018 | 15.07.2018
Ella Doe  | Beowulf   | 16.07.2018 | 01.08.2018
Ella Doe  | King Lear | 02.08.2018 | 31.08.2018
Ella Doe  | Dracula   | 01.09.2018 |    
Emma Doe  | Beowulf   | 01.07.2018 | 15.07.2018
Emma Doe  | Inferno   | 16.07.2018 | 01.08.2018
Emma Doe  | Macbeth   | 02.08.2018 | 31.08.2018
Emma Doe  | Lolita    | 01.09.2018 |    
Jack Doe  | 1984      | 01.07.2018 | 15.07.2018
Jack Doe  | Inferno   | 16.07.2018 | 01.08.2018
Jack Doe  | Othello   | 02.08.2018 | 31.08.2018
Jack Doe  | Dracula   | 01.09.2018 |
Jade Doe  | Lolita    | 01.07.2018 | 15.07.2018
Jade Doe  | Hobbit    | 16.07.2018 | 01.08.2018
Jade Doe  | Hamlet    | 02.08.2018 | 31.08.2018
Jade Doe  | Beowulf   | 01.09.2018 |    
Jane Doe  | Dracula   | 01.07.2018 | 15.07.2018
Jane Doe  | Ulysses   | 16.07.2018 | 01.08.2018
Jane Doe  | Inferno   | 02.08.2018 | 31.08.2018
Jane Doe  | Pygmalion | 01.09.2018 |
John Doe  | Macbeth   | 01.07.2018 | 15.07.2018
John Doe  | Hobbit    | 16.07.2018 | 01.08.2018
John Doe  | Ulysses   | 02.08.2018 | 31.08.2018
John Doe  | Dracula   | 01.09.2018 |    
Noah Doe  | Pygmalion | 01.07.2018 | 15.07.2018
Noah Doe  | Othello   | 16.07.2018 | 01.08.2018
Noah Doe  | Beowulf   | 02.08.2018 | 31.08.2018
Noah Doe  | 1984      | 01.09.2018 |    
Nora Doe  | Dracula   | 01.07.2018 | 15.07.2018
Nora Doe  | Pygmalion | 16.07.2018 | 01.08.2018
Nora Doe  | Hamlet    | 02.08.2018 | 31.08.2018
Nora Doe  | Lolita    | 01.09.2018 |    
Sara Doe  | Beowulf   | 01.07.2018 | 15.07.2018
Sara Doe  | Dracula   | 16.07.2018 | 01.08.2018
Sara Doe  | Ulysses   | 02.08.2018 | 31.08.2018
Sara Doe  | Lolita    | 01.09.2018 |    
Seth Doe  | Macbeth   | 01.07.2018 | 15.07.2018
Seth Doe  | Hamlet    | 16.07.2018 | 01.08.2018
Seth Doe  | King Lear | 02.08.2018 | 31.08.2018
Seth Doe  | Othello   | 01.09.2018 |

我需要一批租莎士比亚的书的用户。

使用此查询：

SELECT USER_RENT,
   COUNT(DISTINCT CASE WHEN BOOK_RENT = 'Hamlet' THEN USER_RENT END) HAMLET,
   COUNT(DISTINCT CASE WHEN BOOK_RENT = 'Othello' THEN USER_RENT END) OTHELLO,
   COUNT(DISTINCT CASE WHEN BOOK_RENT = 'Macbeth' THEN USER_RENT END) MACBETH,
   COUNT(DISTINCT CASE WHEN BOOK_RENT = 'King Lear' THEN USER_RENT END) KING_LEAR
FROM BOOKS
GROUP BY USER_RENT;

我有一张临时桌子：

user_rent | hamlet | othello | macbeth | king_lear
--------------------------------------------------
Alan Doe  |      1 |       1 |       1 |       1
Alex Doe  |      1 |       0 |       0 |       0
Anna Doe  |      0 |       0 |       0 |       1
Ella Doe  |      0 |       0 |       1 |       1
Emma Doe  |      0 |       0 |       1 |       0
Jack Doe  |      0 |       1 |       0 |       0
Jade Doe  |      1 |       0 |       0 |       0
Jane Doe  |      0 |       0 |       0 |       0
John Doe  |      0 |       0 |       1 |       0
Noah Doe  |      0 |       1 |       0 |       0
Nora Doe  |      1 |       0 |       0 |       0
Sara Doe  |      0 |       0 |       0 |       0
Seth Doe  |      1 |       1 |       1 |       1

我导出到excel，然后通过枢轴和过滤器获得了我想要的最终结果(，这是我需要的)：

book_rent                  | number
-----------------------------------
Hamlet                     | 5
Othello                    | 4
Macbeth                    | 5
King Lear                  | 4
Hamlet AND Othello         | 2
Hamlet AND Macbeth         | 2
Hamlet AND King Lear       | 2
Othello AND Macbeth        | 1
Othello AND King Lear      | 2
Macbeth AND King Lear      | 3
H AND O AND M              | 2
H AND O AND K              | 2
H AND M AND K              | 2
O AND M AND K              | 2
All four                   | 2
Hamlet OR Othello          | 7
Hamlet OR Macbeth          | 8
.
.
.

由于我的表要大得多，那么这个例子是否有更优雅和更简单的方法可以直接在SQL中完成呢？

oracle

sql

回答 2

Stack Overflow用户

发布于 2018-10-12 11:05:43

这能做你想做的事吗？

SELECT books, COUNT(*)
FROM (SELECT USER_RENT, LISTAGG(BOOK_RENT, ';') WITHIN GROUP (ORDER BY BOOK_RENT) as books
      FROM BOOKS
      GROUP BY USER_RENT
     ) bu
GROUP BY books;

如果有人租同一本书两次，它会多次出现。你可能想：

SELECT books, COUNT(*)
FROM (SELECT USER_RENT, LISTAGG(BOOK_RENT, ';') WITHIN GROUP (ORDER BY BOOK_RENT) as books
      FROM (SELECT DISTINCT USER_RENT, BOOK_RENT FROM BOOKS) ub
      GROUP BY USER_RENT
     ) bu
GROUP BY books;

票数 1

Stack Overflow用户

发布于 2018-10-12 12:23:54

我的尝试：

/* -- sample data 
with titles(book) as (
  select 'Hamlet'    from dual union all
  select 'Othello'   from dual union all
  select 'Macbeth'   from dual union all
  select 'King Lear' from dual ),
books (usr, book) as (
  select 'Alan', 'Hamlet'    from dual union all
  select 'Alan', 'Othello'   from dual union all
  select 'Alan', 'Macbeth'   from dual union all
  select 'Alan', 'King Lear' from dual union all
  select 'Alan', 'Hamlet'    from dual union all
  select 'Alex', 'Hamlet'    from dual union all
  select 'Anna', 'King Lear' from dual union all
  select 'Ella', 'Macbeth'   from dual union all
  select 'Ella', 'King Lear' from dual union all
  select 'Emma', 'Macbeth'   from dual union all
  select 'Jack', 'Othello'   from dual union all
  select 'Jade', 'Hamlet'    from dual union all
  select 'John', 'Macbeth'   from dual union all
  select 'Noah', 'Othello'   from dual union all
  select 'Nora', 'Hamlet'    from dual union all
  select 'Seth', 'Hamlet'    from dual union all
  select 'Seth', 'Othello'   from dual union all
  select 'Seth', 'Macbeth'   from dual union all
  select 'Seth', 'King Lear' from dual ),
*/ -- end of sample data
with 
  tmp as (
    select grp, sys_connect_by_path(book, '#') path, level cnt
      from (select row_number() over (order by book) grp, book from titles) 
      connect by book > prior book),
  groups as (
    select grp, path, cnt, trim(column_value) book 
      from tmp, xmltable(('"'||replace(ltrim(path,'#'), '#', '","')||'"')))
select path, count(1) cnt, listagg(usr, ', ') within group (order by usr) users
  from (
    select usr, path, grp
      from groups g join (select distinct usr, book from books) b on b.book = g.book
      group by usr, path, cnt, grp
      having cnt = count(1))
  group by path

似乎你想要各种组合的书籍。我使用分层查询，然后将函数sys_connect_by_path()的结果拆分成行来实现这一点。

下一步是使用表books加入这些组，计算每个用户的图书数，如果这个数量低于组中的图书数--将他从结果中删除。

最后，我只对用户进行分组计算，并以列表形式表示。结果：

PATH                                      CNT USERS
---------------------------------- ---------- -------------------------------
#Hamlet                                     5 Alan, Alex, Jade, Nora, Seth
#Hamlet#King Lear                           2 Alan, Seth
#Hamlet#King Lear#Macbeth                   2 Alan, Seth
#Hamlet#King Lear#Macbeth#Othello           2 Alan, Seth
#Hamlet#King Lear#Othello                   2 Alan, Seth
#Hamlet#Macbeth                             2 Alan, Seth
#Hamlet#Macbeth#Othello                     2 Alan, Seth
#Hamlet#Othello                             2 Alan, Seth
#King Lear                                  4 Alan, Anna, Ella, Seth
#King Lear#Macbeth                          3 Alan, Ella, Seth
#King Lear#Macbeth#Othello                  2 Alan, Seth
#King Lear#Othello                          2 Alan, Seth
#Macbeth                                    5 Alan, Ella, Emma, John, Seth
#Macbeth#Othello                            2 Alan, Seth
#Othello                                    4 Alan, Jack, Noah, Seth

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/52774964

复制

相似问题

问类似于排序- SQL Oracle的枢轴
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问类似于排序- SQL Oracle的枢轴EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问类似于排序- SQL Oracle的枢轴
EN