{"action":"closed","issue":{"url":"https://api.github.com/repos/Rdatatable/data.table/issues/3179","repository_url":"https://api.github.com/repos/Rdatatable/data.table","labels_url":"https://api.github.com/repos/Rdatatable/data.table/issues/3179/labels{/name}","comments_url":"https://api.github.com/repos/Rdatatable/data.table/issues/3179/comments","events_url":"https://api.github.com/repos/Rdatatable/data.table/issues/3179/events","html_url":"https://github.com/Rdatatable/data.table/issues/3179","id":386567133,"node_id":"MDU6SXNzdWUzODY1NjcxMzM=","number":3179,"title":"A small note on the order of cube result","user":{"login":"Henrik-P","id":3583910,"node_id":"MDQ6VXNlcjM1ODM5MTA=","avatar_url":"https://avatars3.githubusercontent.com/u/3583910?v=4","gravatar_id":"","url":"https://api.github.com/users/Henrik-P","html_url":"https://github.com/Henrik-P","followers_url":"https://api.github.com/users/Henrik-P/followers","following_url":"https://api.github.com/users/Henrik-P/following{/other_user}","gists_url":"https://api.github.com/users/Henrik-P/gists{/gist_id}","starred_url":"https://api.github.com/users/Henrik-P/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Henrik-P/subscriptions","organizations_url":"https://api.github.com/users/Henrik-P/orgs","repos_url":"https://api.github.com/users/Henrik-P/repos","events_url":"https://api.github.com/users/Henrik-P/events{/privacy}","received_events_url":"https://api.github.com/users/Henrik-P/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/Rdatatable/data.table/milestones/9","html_url":"https://github.com/Rdatatable/data.table/milestone/9","labels_url":"https://api.github.com/repos/Rdatatable/data.table/milestones/9/labels","id":3285663,"node_id":"MDk6TWlsZXN0b25lMzI4NTY2Mw==","number":9,"title":"1.12.0","description":"","creator":{"login":"jangorecki","id":3627377,"node_id":"MDQ6VXNlcjM2MjczNzc=","avatar_url":"https://avatars3.githubusercontent.com/u/3627377?v=4","gravatar_id":"","url":"https://api.github.com/users/jangorecki","html_url":"https://github.com/jangorecki","followers_url":"https://api.github.com/users/jangorecki/followers","following_url":"https://api.github.com/users/jangorecki/following{/other_user}","gists_url":"https://api.github.com/users/jangorecki/gists{/gist_id}","starred_url":"https://api.github.com/users/jangorecki/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jangorecki/subscriptions","organizations_url":"https://api.github.com/users/jangorecki/orgs","repos_url":"https://api.github.com/users/jangorecki/repos","events_url":"https://api.github.com/users/jangorecki/events{/privacy}","received_events_url":"https://api.github.com/users/jangorecki/received_events","type":"User","site_admin":false},"open_issues":76,"closed_issues":37,"state":"open","created_at":"2018-04-21T00:42:12Z","updated_at":"2018-12-05T00:06:09Z","due_on":null,"closed_at":null},"comments":4,"created_at":"2018-12-02T14:56:32Z","updated_at":"2018-12-05T00:06:09Z","closed_at":"2018-12-05T00:06:09Z","author_association":"NONE","body":"In the **Description** in `?cube`, we find that [`cube`]\r\n> Reflects SQLs GROUPING SETS\r\n\r\nThen, the **Value** section is rather vague: \r\n>A data.table with various aggregates\r\n\r\n...but **References** are provided to [PostgreSQL 7.2.4. GROUPING SETS, CUBE, and ROLLUP](https://www.postgresql.org/docs/9.5/queries-table-expressions.html#QUERIES-GROUPING-SETS).\r\n\r\nThere we find that:\r\n\r\n    CUBE ( a, b, c )\r\nis equivalent to\r\n\r\n    GROUPING SETS (\r\n        ( a, b, c ),\r\n        ( a, b    ),\r\n        ( a,    c ),\r\n        ( a       ),\r\n        (    b, c ),\r\n        (    b    ),\r\n        (       c ),\r\n        (         )\r\n    )\r\n\r\nIt seems like the _first_ grouping variable varies _slowest_, and the last variable varies fastest. **Disclaimer**: I don't have access to PostgreSQL, so I can't confirm that this actually reflects the final output ;) \r\n\r\nIn `cube`, it is the other way around: the _last_ grouping variable in `by` varies _slowest_, and the first fastest, like:\r\n\r\n    GROUPING SETS (\r\n        ( a, b, c ),\r\n        (    b, c ),\r\n        ( a,    c ),\r\n        (       c ),\r\n        ( a, b    ),\r\n        (    b    ),\r\n        ( a       ),\r\n        (         )\r\n    )\r\n\r\n\r\nI don't claim that PostgreSQL is \"right\", but _given_ that no explicit **Value** section is provided in `?cube`, and that the help text instead refers to PostgreSQL docs, the order of `cube` output may be considered inconsistent (but again, note my disclaimer above). In addition, I find the PostgreSQL ordering more intuitive, a matter of taste perhaps.\r\n\r\n---------\r\n\r\nAn example to illustrate the order of `cube`:\r\n\r\n    set.seed(1)\r\n    d <- data.table(a = rep(1:2, each = 8),\r\n                    b = rep(1:2, each = 4),\r\n                    c = rep(1:2, each = 2),\r\n                    val = sample(0:1, 16, replace = TRUE))\r\n\r\n    all.equal(\r\n      cube(d, j= sum(val), by = c(\"a\", \"b\", \"c\")),\r\n      groupingsets(d, j = sum(val), by = c(\"a\", \"b\", \"c\"),\r\n                   sets = list(c(\"a\", \"b\", \"c\"),\r\n                               c(     \"b\", \"c\"),\r\n                               c(\"a\",      \"c\"),\r\n                               c(          \"c\"),\r\n                               c(\"a\", \"b\"     ),\r\n                               c(     \"b\"     ),\r\n                               c(\"a\"          ),\r\n                               character()))\r\n    )\r\n    # [1] TRUE\r\n\r\n-----------\r\n\r\n**Update**\r\n\r\nWhen I added an `id`, it's obvious that the counter is in fact based on the PostgreSQL order, which in the current output order becomes non-consecutive. Somewhat odd. It seems to me that the output rather could have the PostgreSQL order right away. \r\n\r\n\r\n    cube(d, j= sum(val), by = c(\"a\", \"b\", \"c\"), id = TRUE)\r\n        grouping  a  b  c V1\r\n     1:        0  1  1  1  0 #  ( a, b, c )\r\n     2:        0  1  1  2  2\r\n     3:        0  1  2  1  1\r\n     4:        0  1  2  2  2\r\n     5:        0  2  1  1  1\r\n     6:        0  2  1  2  0\r\n     7:        0  2  2  1  1\r\n     8:        0  2  2  2  1\r\n     9:        4 NA  1  1  1 # (    b, c )\r\n    10:        4 NA  1  2  2\r\n    11:        4 NA  2  1  2\r\n    12:        4 NA  2  2  3\r\n    13:        2  1 NA  1  1 # ( a,    c )\r\n    14:        2  1 NA  2  4\r\n    15:        2  2 NA  1  2\r\n    16:        2  2 NA  2  1\r\n    17:        6 NA NA  1  3 # (       c )\r\n    18:        6 NA NA  2  5\r\n    19:        1  1  1 NA  2 # ( a, b    )\r\n    20:        1  1  2 NA  3\r\n    21:        1  2  1 NA  1\r\n    22:        1  2  2 NA  2\r\n    23:        5 NA  1 NA  3 # (    b    )\r\n    24:        5 NA  2 NA  5\r\n    25:        3  1 NA NA  5 # ( a       )\r\n    26:        3  2 NA NA  3\r\n    27:        7 NA NA NA  8 # (         )\r\n     grouping  a  b  c V1"}}
{"actor":{"display_login":"mattdowle"}}