7 ms·

I definitely see why (a) is good for time-series data, but I (personally) think that it shouldn't be a first-class part of a relational query language; I think

• beagle3 14y ago
  > good for time-series data, but I (personally) think that it shouldn't be a first-class part of a relational query language; First of all, there's some tautology here - a "relational database" (and similarly, relational calculus, relational algebra, etc), BY DEFINITION deal with sets of tuples ("relations") which are (again, by definition) unordered. So let's just ignore the word "relational" in this discussion. > I think most databases do support the ROW_NUMBER() function now though. Many do. But do compare (sql 2005): SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS ROW_NUMBER, COLUMNS FROM tablename ) foo WHERE ROW_NUMBER <= 10 To (kdb+ / q): select from tablename orderby asc sort_key where i<10; And usefulness of order goes why beyond time-series data (and sorting): let's say you have a tiered pricing scheme for widgets you sell: order_size | per_widget_cost ---------------------------- 1-9 | $2 10-49 | $1.9 50-199 | $1.8 Without embracing order, you: a) duplicate the range data (have a "from-count", "to-count" fields for each record, risking that you might have holes or overlaps) b) not duplicate data, but have crazy subselects (among all with count > from_count, select the one with maximum to_count) or stuff like that. When you actually have order, you have operator that embrace that order - e.g. kdb+/q's "bin" which finds the "bin" (as in "bucket", not as in binray) something fits in: select unit_price[from_count bin order_count] from table There are many other use cases involving running sums (e.g, you have a table of weights and priority; select the list of highest priority items whose weight sums to 100lbs or less). Order is really really missing in SQL, but it's one of those things people are not aware of because they've never used anything that does support it properly. nested columns just means what it sounds like: that you can put anything in a cell (including lists, tables, lists of tables, lists of lists of lists of tables). Many one-to-many relationships in sql that need additional tables can just be done within the same table in kdb+/q regarding Common Table Expressions - I wasn't aware of them, they do help a lot. The syntax is horrible, but I guess they do work... edit: more info on kdb+/q can be found in http://kx.com/q/d/kdb+1.htm http://kx.com/q/d/kdb+1.htm - you have to get to section 8 before they start discussing the query language, but it's short and to the point. There's a lot more in http://kx.com/q/d/ http://kx.com/q/d/ if you are interested.
  • einhverfr 14y ago
    Without embracing order, you: a) duplicate the range data (have a "from-count", "to-count" fields for each record, risking that you might have holes or overlaps) That's never how I have done it. Since this is a tiered pricing model, you just: select * from widget_price where min_size < ? ORDER BY min_size desc limit 1; We actually do something almost identical for sales tax rate changes in LedgerSMB. You look to the date of the transaction and take the most recent rate. No range types, etc. Where range types are handy (and why I am looking forward to them in 9.2) is where you have to deal with things like a financial transaction that should be amortized over a period of time. This would allow you to adjust the transaction incrementally as a reporting, rather than an accounting, function. But I wouldn't use them for cases where you don't want overlaps or gaps. The best thing there is to just put in floor values and select the next highest floor. There you go. Again order by and limit/offset do pretty much what you want without doing crazy inline views like you are doing in your example. BTW, I see inline views as an antipattern in SQL best avoided if you can. nested columns just means what it sounds like: that you can put anything in a cell (including lists, tables, lists of tables, lists of lists of lists of tables). PostgreSQL supports this btw although back in 7.3 or so if I remember, I found that tables with tuples in columns were write-only but that was fixed pretty quickly (first with a "don't do that" check and then with a real fix). The point of CTE's is to give you a stable intermediate result set.
    • beagle3 14y ago
      .... and you've just embraced (in a limited inconsistent way). using "ORDER" is outside of the relational model, where order plays no part. ORDER was there from the beginning, but CTEs, nested columns, window functions, connect-by recursive selects and similar stuff is being added because SQL and the relational model are actually quite limited when it comes to real world problems. Of course, SQL will keep getting extended to solve real world problems; however, that does not mean SQL is "the best solution out there" (or "the worst solution except all others that were tried").
      • einhverfr 14y ago
        There are certain things I really like about SQL at least compared to other programming languages. I am not sure it is the best possible db query language. Personally I have always thought Quel was elegant. However compared to app languages, I would far rather spend my time debugging 200-1000 line SQL statements than 200-1000 line subroutines in Perl, Ruby, or C++.....