{"id":111170,"date":"2016-04-26T13:27:04","date_gmt":"2016-04-26T13:27:04","guid":{"rendered":"http:\/\/www.timestored.com\/b\/?p=111170"},"modified":"2016-04-27T11:44:43","modified_gmt":"2016-04-27T11:44:43","slug":"standard-sql-sucks-and-this-is-why","status":"publish","type":"post","link":"https:\/\/www.timestored.com\/b\/standard-sql-sucks-and-this-is-why\/","title":{"rendered":"Standard SQL sucks and this is Why"},"content":{"rendered":"<p><img loading=\"lazy\" src=\"https:\/\/www.timestored.com\/b\/wp-content\/uploads\/2016\/04\/dbfire.png\" alt=\"dbfire\" width=\"140\" height=\"171\" class=\"alignnone size-full wp-image-111181\" style='float:right'\/><\/p>\n<p>Recently there was a post on <a href=\"https:\/\/blog.jooq.org\/2016\/04\/25\/10-sql-tricks-that-you-didnt-think-were-possible\/\">SQL tips by the JOOQ guys<\/a>. I love their work but I think standard SQL is not the solution to many of these problems. What we need is something new or in this case old, that is built for such queries. What do I mean, well let&#8217;s look through their examples reimplemented in qsql and I&#8217;ll show you how much shorter and simpler this could be.<\/p>\n<h3>Everything is a table<\/h3>\n<p>In kdb we take this a step further and make tables standard variables, no special notation\/treatment, it&#8217;s a variable like any other variable in your programming language. Instead of messing about with value()() we define a concise notation to define our variables like so:<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-1'>\r\nq)person:([] a:1 2 3)\r\nq)person\r\na\r\n-\r\n1\r\n2\r\n3\r\nq)select from person\r\na\r\n-\r\n1\r\n2\r\n3\r\nq)person+2\r\na\r\n-\r\n3\r\n4\r\n5\r\n\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-1'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>Data Generation with Recursive SQL<\/h3>\n<p>This is the example syntax they have used to define two tables and then join them:<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-2'>\r\nWITH\r\n  t1(v1, v2) AS (SELECT 1, 2),\r\n  t2(w1, w2) AS (\r\n    SELECT v1 * 2, v2 * 2\r\n    FROM t1\r\n  )\r\nSELECT *\r\nFROM t1, t2\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-2'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>What to hell! If I want variables, let&#8217;s have proper variables NOT &#8220;Common Table Expressions&#8221;.<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-3'>\r\nq)a:([] v1:1 2; v2:3 4)\r\nq)b:([] v3:5 6; v4:7 8)\r\nq)a\r\nv1 v2\r\n-----\r\n1  3\r\n2  4\r\nq)b\r\nv3 v4\r\n-----\r\n5  7\r\n6  8\r\nq)a,'b\r\nv1 v2 v3 v4\r\n-----------\r\n1  3  5  7\r\n2  4  6  8\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-3'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>I created two tables a and b then I joined them sideways. See how simple that was.<\/p>\n<h3>Running Total Calculations<\/h3>\n<p>Oh dear SQL how badly you have chosen your examples. Running calculations are to APL\/qSQL as singing is to Tom Jones, we do it everyday all day and we like it. In fact the example doesn&#8217;t even give the full code. See this <a href=\"http:\/\/stackoverflow.com\/questions\/14953294\/how-to-get-running-sum-of-a-column-in-sql-server\">SO post<\/a> for how these things get implemented. e.g. Standard SQL Running Sum<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-4'>\r\nselect a.ICode, \r\n       sum(sum(isnull(a.qty, 0))) OVER (partition by a.icode order by a.icode) AS total\r\nfrom t_Stock a\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-4'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>qSQL table Definition and Running Sum:<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-5'>\r\nq)t:([] a:0 0 1 1 1 1 1 1 0 0; b:2 3 5 7 9 11 13 15 1 100)\r\nq)update runningSumB:sums b by a from t\r\na b   runningSumB\r\n-----------------\r\n0 2   2\r\n0 3   5\r\n1 5   5\r\n1 7   12\r\n1 9   21\r\n1 11  32\r\n1 13  45\r\n1 15  60\r\n0 1   6\r\n0 100 106\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-5'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>Finding the Length of a Series<\/h3>\n<p>This is their code:<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-8'>\r\nSELECT 1 + hi - lo AS length,\r\n  trx.*,\r\n  coalesce(last_value (lo) IGNORE NULLS OVER (\r\n    ORDER BY id DESC\r\n    ROWS BETWEEN UNBOUNDED PRECEDING \r\n    AND CURRENT ROW), rn) AS lo,\r\n  coalesce(first_value(hi) IGNORE NULLS OVER (\r\n    ORDER BY id DESC\r\n    ROWS BETWEEN CURRENT ROW \r\n    AND UNBOUNDED FOLLOWING), rn) AS hi\r\nFROM trx\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-8'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>This is KDB:<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='suk-sql-9'>\r\nq)update length:count i by sums 0<>deltas 0<amount from t\r\namount length\r\n-------------\r\n24     1\r\n-32    2\r\n-13    2\r\n27     1\r\n-22    2\r\n-24    2\r\n34     1\r\n0      3\r\n-13    3\r\n-20    3\r\n25     3\r\n11     3\r\n6      3\r\n-27    2\r\n-4     2\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('suk-sql-9'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>In <a href=\"https:\/\/www.youtube.com\/watch?v=8kUQWuK1L4w\">1974 Ken Iverson gave a talk on APL<\/a>. He described how he reduced it down to a core set of operations that everything could be made from. Using these simple building blocks you could make some really cool things. It&#8217;s sad to think we may not have came that far. <\/p>\n<p>qSQL\/kdb is a database based on the concept of ordered lists, carrying over many ideas from APL that make array operations shorter and simpler. If you like what you see we <a href=\"http:\/\/www.timestored.com\/kdb-guides\/kdb-database-intro\">provide tutorials on kdb<\/a> to get started, this <a href=\"http:\/\/www.timestored.com\/kdb-guides\/kdb-database-intro\">intro<\/a> is a good place to get started.<\/p>\n<p>We also have <a href=\"http:\/\/www.timestored.com\/kdb-training\/free-student-access\">free online kdb training for students<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently there was a post on SQL tips by the JOOQ guys. I love their work but I think standard SQL is not the solution to many of these problems. What we need is something new or in this case old, that is built for such queries. What do I mean, well let&#8217;s look through [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[2],"tags":[97,32],"_links":{"self":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111170"}],"collection":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/comments?post=111170"}],"version-history":[{"count":11,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111170\/revisions"}],"predecessor-version":[{"id":111179,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111170\/revisions\/111179"}],"wp:attachment":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/media?parent=111170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/categories?post=111170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/tags?post=111170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}