sql - Oracle SQL 顺序组号赋值

Question

我有一个结果集，为简单起见，我将其称为包含三列的表“选项卡”:类别、子类别和日期，按类别和日期排序。该数据集是一个网格，我希望在该网格之上执行其他处理。我的问题是唯一标识(或顺序标记)数据集中的组。基于前 3 列的存在，下面的 SQL 是我所追求的(GID1 或 GID2 都可以)。我已经尝试过 group_id、grouping_id、rank、dense_rank，要么错过了其中一个技巧，要么正在尝试一些非常尴尬的事情。 GID 的顺序并不重要，但重要的是组号分配基于排序的数据(类别然后是日期)。

 CREATE TABLE Tab
        ("Category" varchar2(1), "SubCategory" varchar2(7), "Date" int, "GID1" int, "GID2" int);

    INSERT ALL 
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'bannana', 20120101, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'grape', 20120102, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'pear', 20120103, 1, 1)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'pear', 20120104, 1, 1)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'bannana', 20120105, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'pear', 20120106, 2, 2)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'pear', 20120107, 2, 2)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'apple', 20120108, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('A', 'pear', 20120109, 3, 3)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'apple', 20120101, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'bannana', 20120102, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'apple', 20120103, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'bannana', 20120104, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'pear', 20120105, 1, 4)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'pear', 20120106, 1, 4)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'pear', 20120107, 1, 4)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'pear', 20120108, 1, 4)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('B', 'pear', 20120109, 1, 4)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'grape', 20120101, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'grape', 20120102, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'apple', 20120103, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'bannana', 20120104, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'grape', 20120105, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'pear', 20120106, 1, 5)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'apple', 20120107, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'apple', 20120108, NULL, NULL)
        INTO Tab ("Category", "SubCategory", "Date", "GID1", "GID2")
             VALUES ('C', 'apple', 20120109, NULL, NULL)
    SELECT * FROM dual
    ;

Answer 1

好吧，如果它只是梨的话:

SQL> select "Category", "SubCategory", "Date",
  2         case
  3           when "SubCategory" = 'pear'
  4           then
  5             count(rn) over (partition by "Category" order by "Date") else null
  6         end GID1 ,
  7         case
  8           when "SubCategory" = 'pear'
  9           then
 10             count(rn) over ( order by "Category", "Date") else null
 11         end GID2
 12    from (select "Category", "SubCategory", "Date", lag("SubCategory") over (partition by "Category" order by "Date"),
 13                                    case
 14                                      when lag("SubCategory") over (partition by "Category" order by "Date") != "SubCategory"
 15                                      and "SubCategory" = 'pear'
 16                                       then 1
 17                                      when row_number() over (partition by "Category" order by "Date") = 1 and "SubCategory" = 'pear' then 1
 18                                      else null
 19                                    end rn
 20                               from tab)
 21   order by 1, 3;

Category   SubCate       Date       GID1       GID2
---------- ------- ---------- ---------- ----------
A          bannana   20120101
A          grape     20120102
A          pear      20120103          1          1
A          pear      20120104          1          1
A          bannana   20120105
A          pear      20120106          2          2
A          pear      20120107          2          2
A          apple     20120108
A          pear      20120109          3          3
B          apple     20120101
B          bannana   20120102
B          apple     20120103
B          bannana   20120104
B          pear      20120105          1          4
B          pear      20120106          1          4
B          pear      20120107          1          4
B          pear      20120108          1          4
B          pear      20120109          1          4
C          grape     20120101
C          grape     20120102
C          apple     20120103
C          bannana   20120104
C          grape     20120105
C          pear      20120106          1          5
C          apple     20120107
C          apple     20120108
C          apple     20120109

打破这个。

我们查看按“日期”排序的前一行(对于每个“类别”)，看看它是否是不同的“子类别”以及当前类别 = pear。如果是这样，我们用“1”标记该行(与我们使用的内容无关，只是 NON NULL)。

lag("SubCategory") over (partition by "Category" order by "Date") != "SubCategory" 
 and "SubCategory" = 'pear'

同样对于第一行，我们分配相同的。这给了我们:

Category   SubCate       Date LAG("SU         RN
---------- ------- ---------- ------- ----------
A          bannana   20120101
A          grape     20120102 bannana
A          pear      20120103 grape            1
A          pear      20120104 pear
A          bannana   20120105 pear
A          pear      20120106 bannana          1
A          pear      20120107 pear
A          apple     20120108 pear
A          pear      20120109 apple            1
B          apple     20120101
B          bannana   20120102 apple
B          apple     20120103 bannana
B          bannana   20120104 apple
B          pear      20120105 bannana          1
B          pear      20120106 pear
B          pear      20120107 pear
B          pear      20120108 pear
B          pear      20120109 pear
C          grape     20120101
C          grape     20120102 grape
C          apple     20120103 grape
C          bannana   20120104 apple
C          grape     20120105 bannana
C          pear      20120106 grape            1
C          apple     20120107 pear
C          apple     20120108 apple
C          apple     20120109 apple

现在，我们简单地计算()非空“RN”值在日期上再次排序(GID1 的每个类别，而不是 GID2 [gid2 我们也按它排序!)。这是这些行:count(rn) over(按“类别”分区，按“日期”排序) (GID1)

和count(rn) 超过(按“类别”、“日期”排序) (GID2)