×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

你这写法似乎有问题?

本文发表在 rolia.net 枫下论坛SELECT @max = maxSequence from #seqMax

难度不应该是这样:

SELECT @max = MAX(maxSequence) from #seqMax

同样的思路用ORACLE实现:

CREATE TABLE SourceTable(Item VARCHAR2(10),Line NUMBER, seq NUMBER);

INSERT INTO SourceTable VALUES ('A',1,0);
INSERT INTO SourceTable VALUES ('A',1,1);
INSERT INTO SourceTable VALUES ('A',1,2);
INSERT INTO SourceTable VALUES ('A',1,3);
INSERT INTO SourceTable VALUES ('A',1,4);
INSERT INTO SourceTable VALUES ('A',1,5);

INSERT INTO SourceTable VALUES ('B',1,0);
INSERT INTO SourceTable VALUES ('B',1,1);
INSERT INTO SourceTable VALUES ('B',1,2);
INSERT INTO SourceTable VALUES ('B',1,3);
INSERT INTO SourceTable VALUES ('B',1,4);
INSERT INTO SourceTable VALUES ('B',1,5);
INSERT INTO SourceTable VALUES ('B',1,8);
INSERT INTO SourceTable VALUES ('B',1,9);

INSERT INTO SourceTable VALUES ('A',2,1);
INSERT INTO SourceTable VALUES ('A',2,2);
INSERT INTO SourceTable VALUES ('A',2,5);
INSERT INTO SourceTable VALUES ('A',2,7);
INSERT INTO SourceTable VALUES ('A',2,8);


SELECT b.item,b.line,a.n
FROM (SELECT LEVEL-1 n FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(seq)+1 FROM SourceTable)) a
LEFT JOIN (SELECT s.*,MAX(seq) OVER(PARTITION BY item,line) maxseq FROM SourceTable s) b
PARTITION BY (b.item,b.line,b.maxseq)
ON (A.n=b.seq)
WHERE b.seq IS NULL AND a.n<=b.maxseq;

ITEM LINE N
---------- ---------- ----------
A 2 0
A 2 3
A 2 4
A 2 6
B 1 6
B 1 7

6 rows selected.

ORACLE的PARTITION OUTER JOIN免除了中间一次笛卡尔积。更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / It seems that many people believe the mathematics is very important to IT staff. I don’t have any experience using any deep math theory for IT tasks.
    One of my colleagues asked me to give a solution for following question.

    In one data set (actually a data table), there are three fields, Item, Line Number, and Sequence.

    In the data set, one Item has one or many Lines, and one line has a sequence starting from zero.

    After some records were removed, we need find out missing sequence numbers. If a record in a maximum sequence number is removed, the whole sequence of the Item/Line is still in a sequential order, so it’s ok. However, if it’s not a maximum sequence number, the number is missing.

    I believe that it’s not too difficult to get the result either by SQL or by LINQ. Could anyone give us solution by math or by an approach based on any math theory?
    • 兴趣还是最重要的,有了兴趣自然会去学那些该用到的数学
      • At least give one your example.
    • 看做什么啦,在微软开发SQL或LINQ就要想着如何查询得快。
      • Don’t fully understand.
        You are not going to say the slower the better, do you?
    • 把你写的SQL秀一下看看?SQL不就是关系代数的一种实现吗?
      • You are expert of SQL. It’s just B. M. N. F. to show SQL code to you.
        • it's similar to this SQL quiz, but I only know Oracle syntax and would like to know how it looks like in SQL Server
          http://www.itpub.net/forum.php?mod=redirect&goto=findpost&ptid=1506936&pid=19372285

          see the quiz in #191.
          • Although the quiz looks similar to my question, due to different target, my idea of the solution is completely different.
            SELECT Item, Line, MAX(sequence) maxSequence INTO #seqMax FROM SourceTable
            Group by Item, Line
            HAVING MAX(sequence) > COUNT(*) +1
            --Although it can get same result without above HAVING condition, it improves performance for large amount data.

            declare @max int
            SELECT @max = maxSequence from #seqMax
            declare @i int
            create table #seqNum (sequence int)
            SELECT @i =0
            while @i <=@max
            Begin
            insert #seqNum values (@i)
            set @i = @i +1
            End

            SELECT M.Item, M.Line, S.sequence
            FROM #seqNum S JOIN #seqMax M ON S.sequence <= M.maxSequence
            Left JOIN SourceTable I ON I.Item = M.Item AND I.Line = M.Line AND I.sequence = S.sequence
            Where I.sequence IS NULL
            ORDER BY M.Item, M.Line, S.sequence
            • 你这写法似乎有问题?
              本文发表在 rolia.net 枫下论坛SELECT @max = maxSequence from #seqMax

              难度不应该是这样:

              SELECT @max = MAX(maxSequence) from #seqMax

              同样的思路用ORACLE实现:

              CREATE TABLE SourceTable(Item VARCHAR2(10),Line NUMBER, seq NUMBER);

              INSERT INTO SourceTable VALUES ('A',1,0);
              INSERT INTO SourceTable VALUES ('A',1,1);
              INSERT INTO SourceTable VALUES ('A',1,2);
              INSERT INTO SourceTable VALUES ('A',1,3);
              INSERT INTO SourceTable VALUES ('A',1,4);
              INSERT INTO SourceTable VALUES ('A',1,5);

              INSERT INTO SourceTable VALUES ('B',1,0);
              INSERT INTO SourceTable VALUES ('B',1,1);
              INSERT INTO SourceTable VALUES ('B',1,2);
              INSERT INTO SourceTable VALUES ('B',1,3);
              INSERT INTO SourceTable VALUES ('B',1,4);
              INSERT INTO SourceTable VALUES ('B',1,5);
              INSERT INTO SourceTable VALUES ('B',1,8);
              INSERT INTO SourceTable VALUES ('B',1,9);

              INSERT INTO SourceTable VALUES ('A',2,1);
              INSERT INTO SourceTable VALUES ('A',2,2);
              INSERT INTO SourceTable VALUES ('A',2,5);
              INSERT INTO SourceTable VALUES ('A',2,7);
              INSERT INTO SourceTable VALUES ('A',2,8);


              SELECT b.item,b.line,a.n
              FROM (SELECT LEVEL-1 n FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(seq)+1 FROM SourceTable)) a
              LEFT JOIN (SELECT s.*,MAX(seq) OVER(PARTITION BY item,line) maxseq FROM SourceTable s) b
              PARTITION BY (b.item,b.line,b.maxseq)
              ON (A.n=b.seq)
              WHERE b.seq IS NULL AND a.n<=b.maxseq;

              ITEM LINE N
              ---------- ---------- ----------
              A 2 0
              A 2 3
              A 2 4
              A 2 6
              B 1 6
              B 1 7

              6 rows selected.

              ORACLE的PARTITION OUTER JOIN免除了中间一次笛卡尔积。更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • 抛砖引玉一下。不算算法。如果不用DataView,自己做个快速排序的方法,那就是算法了。
      class Program
      {
      static void Main(string[] args)
      {
      DataTable DT = new DataTable();
      //数据产生过程
      ..........



      DataView DV = DT.DefaultView ;
      DV.Sort = "Item,line,sequence";
      int i, j;
      for (i = 0; i < DV.Count - 1; i++)
      {
      if (DV[i]["Item"] == DV[i + 1]["Item"] && DV[i]["line"] == DV[i + 1]["line"])
      {
      for (j = (int)DV[i]["sequence"] + 1; j < (int)DV[i + 1]["sequence"] ; j++)
      {
      Console.WriteLine(DV[i]["Item"] + " || "+DV[i]["line"] +" || " + j.ToString());
      }
      }
      }

      }
      }
    • My point is not how to achieve it. Rather than, when we think our solution, do we try to use any math theory help our brainstorm?
      • in most cases simple arithmetics will do it so why bother to employ math? like this one is just sorting of a data set then compare the values with the neighbour rows.
        • First of all, can you compare 100 thousands records by eyes? Second, in what criterion you define simple or complex.
          • it's simple and repeated,that's why we use computer. for me, "simple" means anything that can be solved by high school math
    • 前两年无意间读了codd 的那篇文章,然后看了些SQL的书籍,玩了一下as400,感觉真正的数学在数据库内部,然后对SQL就彻底没兴趣了。
      • Most IT people know math is base of IT.
        As newkid mentioned that underneath of SQL is relational algebra. Though, in actual SQL practice, does anyone apply relational algebra solution to data searching question then translate the solution to SQL commands?

        And actually here many IT people are excellent developer/analyser, system/networking/database administers even they did not systemically learn IT/ math.

        Our Chinese people over-emphasize knowledge of base theories but ignore practical skills especially real world skills.

        BTW, AS400 is a platform rather than a database.
        • I'm not an IT guy.
          Math is a tool for most sciences and technologies, including IT.

          Actual SQL practice, in my understanding, belongs to part of software engineering. It is more an art than a science. Experience plays a critical role in such practice.

          IT practitioners are not mathematicians, and they needn't to be a mathematician before doing their jobs. I personally am more interested in doing research job, but this doesn't mean researches are more important than industry practices.

          Many Chinese people put more attention on theories than practical skills. This is not right. I believe that a theory is nearly useless in industry if it can't be implemented with reasonable amount of time and money.

          I ever played DB2 on AS/400, correct me if I remember it wrongly.
          • In terms R&D works in many fields, math is a tool, IT technologies, such as SQL, programming, are tools too (if needed). But math is used to help building model of R&D target rather than to help SQL or programming.
            • Yep, those tools tackle different problems. Process calculus/algebra sounds like the math for modelling concurrent systems, but writing real-time software with process calculus is very expensive.
              Software programming is such a complicated process that hasn't been fully understood. I'm afraid there won't be practical math theory for that in near future, otherwise a software tool may generate another software given user requirements as input. :-)
              • If it was true, I'm afraid
                it’s close to end of the world, like Terminator.
    • Here is another simply question on matrix: graph can be represented as adjacency matrix. Now I understand but still don't understand why, the square of this matrix means the # of pathways between nodes.
      And more importantly, what about the matrix multiply the transpose of itself?

      Thanks in advance!