4 ms·

Windowing functions in standard SQL databases are like putting lipstick on a pig. You created a design based on sets then later tried to slap on the idea of ord

Windowing functions in standard SQL databases are like putting lipstick on a pig. You created a design based on sets then later tried to slap on the idea of order to sets. kdb is based on the concept of ordered lists, not sets and makes windowing functions on time and order based queries ridiculously easy. If this kind of thing interests you I recommend checking it out these comparison queries between kdb and standard SQL: http://www.timestored.com/b/kdb-qsql-query-vs-sql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/ Unfortunately kdb is very expensive.

saosebastiao 10y ago
There is far more utility to window functions than ordering.
gshulegaard 10y ago
> Windowing functions in standard SQL databases are like putting lipstick on a pig. I have always thought the underlying Relational Algebra of SQL databases to be quite beautiful (https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra). This doesn't make it the right tool for every job, but let's try to shy away from calling it a, "pig". > You created a design based on sets then later tried to slap on the idea of order to sets. I have some questions here. It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? * https://en.wikipedia.org/wiki/Partially_ordered_set https://en.wikipedia.org/wiki/Partially_ordered_set * https://en.wikipedia.org/wiki/Total_order https://en.wikipedia.org/wiki/Total_order From the second reference on Total Order: > "The lexicographical order on the Cartesian product of a family of totally ordered sets, indexed by a well ordered set, is itself a total order." This quote seems to actually contradict this implication...especially in respect to Relational Algebra which relies heavily on Cartesian Product. Thanks in advance for your response! I enjoy improving my own understanding of the underlying Computer Science/Mathematics whenever I can.
- RyanHamilton 10y ago
  >>It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? The concept of sets and order may not be mutually exclusive in the mathematical theory but in terms of how actual databases were conceived and implemented they were very far apart. Early on many databases actually took advantage of the fact the operations were set based and that order was not guaranteed to achieve a number of speed optimizations. How long did many databases take to get row number support? "partition by" support? Take a look at this SO post for doing a running sums calculation: http://stackoverflow.com/questions/14953294/how-to-get-running-sum-of-a-column-in-sql-server http://stackoverflow.com/questions/14953294/how-to-get-runni... This is the same kdb code: update runningSumB:sums b by a from t Instead many people ended up turning to cursors to allow ordered calculations.
  - gshulegaard 10y ago
    > The concept of sets and order may not be mutually exclusive in the mathematical theory but in terms of how actual databases were conceived and implemented they were very far apart. I may be wrong, but it was my understanding the original Relational Databases were very much based on the Relational Algebra originally pioneered by E.F. Codd[1]. So I think it is a stretch to say that they were "conceived" separate from the mathematics. Further, it was my understanding that E.F. Codd took his relational algebra which he developed while working as a researcher at IBM and applied it to an implementation of an RDBMS also at IBM (now known as IBM DB2)[2]. So similarly, I think it is a stretch to claim that databases were originally implemented "far apart" from the mathematics. I think there is a valid point that actual DB implementations may be lacking, but isn't that a reason not to use that particular implementation rather than a knock against Relational models? I may be wrong, but it seems like your problems aren't with the Relational Model but rather with potentially lacking/incomplete implementations of it. [1] https://en.wikipedia.org/wiki/Relational_database https://en.wikipedia.org/wiki/Relational_database [2] https://en.wikipedia.org/wiki/IBM_DB2 https://en.wikipedia.org/wiki/IBM_DB2

saosebastiao 10y ago

There is far more utility to window functions than ordering.

gshulegaard 10y ago

> Windowing functions in standard SQL databases are like putting lipstick on a pig. I have always thought the underlying Relational Algebra of SQL databases to be quite beautiful (https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra). This doesn't make it the right tool for every job, but let's try to shy away from calling it a, "pig". > You created a design based on sets then later tried to slap on the idea of order to sets. I have some questions here. It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? * https://en.wikipedia.org/wiki/Partially_ordered_set https://en.wikipedia.org/wiki/Partially_ordered_set * https://en.wikipedia.org/wiki/Total_order https://en.wikipedia.org/wiki/Total_order From the second reference on Total Order: > "The lexicographical order on the Cartesian product of a family of totally ordered sets, indexed by a well ordered set, is itself a total order." This quote seems to actually contradict this implication...especially in respect to Relational Algebra which relies heavily on Cartesian Product. Thanks in advance for your response! I enjoy improving my own understanding of the underlying Computer Science/Mathematics whenever I can.

RyanHamilton 10y ago
>>It sounds like you are implying that sets and order are mutually exclusive, but this is not my understanding of the math. Could you elaborate? The concept of sets and order may not be mutually exclusive in the mathematical theory but in terms of how actual databases were conceived and implemented they were very far apart. Early on many databases actually took advantage of the fact the operations were set based and that order was not guaranteed to achieve a number of speed optimizations. How long did many databases take to get row number support? "partition by" support? Take a look at this SO post for doing a running sums calculation: http://stackoverflow.com/questions/14953294/how-to-get-running-sum-of-a-column-in-sql-server http://stackoverflow.com/questions/14953294/how-to-get-runni... This is the same kdb code: update runningSumB:sums b by a from t Instead many people ended up turning to cursors to allow ordered calculations.
- gshulegaard 10y ago
  > The concept of sets and order may not be mutually exclusive in the mathematical theory but in terms of how actual databases were conceived and implemented they were very far apart. I may be wrong, but it was my understanding the original Relational Databases were very much based on the Relational Algebra originally pioneered by E.F. Codd[1]. So I think it is a stretch to say that they were "conceived" separate from the mathematics. Further, it was my understanding that E.F. Codd took his relational algebra which he developed while working as a researcher at IBM and applied it to an implementation of an RDBMS also at IBM (now known as IBM DB2)[2]. So similarly, I think it is a stretch to claim that databases were originally implemented "far apart" from the mathematics. I think there is a valid point that actual DB implementations may be lacking, but isn't that a reason not to use that particular implementation rather than a knock against Relational models? I may be wrong, but it seems like your problems aren't with the Relational Model but rather with potentially lacking/incomplete implementations of it. [1] https://en.wikipedia.org/wiki/Relational_database https://en.wikipedia.org/wiki/Relational_database [2] https://en.wikipedia.org/wiki/IBM_DB2 https://en.wikipedia.org/wiki/IBM_DB2