Oracle
Oracle - 按組計數組
我有以下查詢:
SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*) "Count" FROM "MyTable" WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13) GROUP BY substr("Timestamp", 1, 16), "Action" ORDER BY substr("Timestamp", 1, 16) DESC, "Action"
我想對組進行分頁,以便例如獲得第 1 頁和第 2 頁的前 2 個組以及相應的元素。
為了更好地理解,我設置了一個小提琴。請參閱:http ://rextester.com/GLY48637
我怎樣才能讓這個分頁工作?
我找到了解決我的問題的方法。我可以建構一個“組索引”並僅跳過/獲取索引中的元素。請參閱以下腳本:
SELECT * FROM ( SELECT x.*, ceil((rank() over (order by x."Timestamp") + count(1) over (partition by x."Timestamp"))/6) as pageindex FROM ( SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*) "Count" FROM "MyTable" WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13) GROUP BY substr("Timestamp", 1, 16), "Action" ORDER BY substr("Timestamp", 1, 16) DESC, "Action" ) x ) WHERE pageindex > (:page - 1) * :pageSize and pageIndex <= :page * :pageSize