{"action":"created","comment":{"url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/comments/32563825","id":32563825,"diff_hunk":"@@ -1,50 +1,78 @@\n (ns puppetlabs.puppetdb.query.aggregate-event-counts\n   (:require [puppetlabs.puppetdb.query.event-counts :as event-counts]\n+            [clojure.string :as str]\n+            [puppetlabs.puppetdb.query.events :as events]\n             [puppetlabs.puppetdb.jdbc :as jdbc]\n             [puppetlabs.kitchensink.core :as kitchensink]))\n \n (defn- get-aggregate-sql\n   \"Given the `event-count-sql`, return a SQL string that will aggregate the results.\"\n-  [event-count-sql]\n+  [event-count-sql summarize_by]\n   {:pre  [(string? event-count-sql)]\n    :post [(string? %)]}\n-  (format \"SELECT SUM(CASE WHEN successes > 0 THEN 1 ELSE 0 END) as successes,\n+  (format \"(SELECT CAST('%s' AS VARCHAR(20)) as summarize_by,\n+           SUM(CASE WHEN successes > 0 THEN 1 ELSE 0 END) as successes,\n                   SUM(CASE WHEN failures > 0  THEN 1 ELSE 0 END) as failures,\n                   SUM(CASE WHEN noops > 0 THEN 1 ELSE 0 END) as noops,\n                   SUM(CASE WHEN skips > 0 THEN 1 ELSE 0 END) as skips,\n                   COUNT(*) as total\n-           FROM (%s) event_counts\" event-count-sql))\n+           FROM (%s) event_counts)\" summarize_by event-count-sql))\n \n-(defn query->sql\n-  \"Convert an aggregate-event-counts `query` and a value to `summarize_by` into a SQL string.\n-  Since all inputs are forwarded to `event-counts/query->sql`, look there for proper documentation.\"\n+(defn assemble-aggregate-sql\n+  \"Convert an aggregate-event-counts `query` and a value to `summarize_by`\n+   into a SQL string.\"\n   [version query [summarize_by query-options]]\n   {:pre  [(sequential? query)\n           (string? summarize_by)\n-          ((some-fn map? nil?) query-options)]\n-   :post [(jdbc/valid-jdbc-query? (:results-query %))]}\n+          ((some-fn map? nil?) query-options)]}\n   (let [query-options (if (nil? query-options) {} query-options)\n         [count-sql & params] (:results-query\n-                              (event-counts/query->sql version query [summarize_by query-options {}]))\n-        aggregate-sql        (get-aggregate-sql count-sql)]\n-    {:results-query (apply vector aggregate-sql params)}))\n+                              (event-counts/query->sql\n+                                :aggregate-event-counts\n+                                version\n+                                query [summarize_by query-options {}]))]\n+    [(get-aggregate-sql count-sql summarize_by) params]))\n+\n+(defn query->sql\n+  \"Convert an aggregate-event-counts `query` and a value(s) to `summarize_by`\n+   into a SQL string. Since all inputs are forwarded to\n+   `event-counts/query->sql`, look there for proper documentation.\"\n+  [version query [summarize_by {:keys [distinct_resources?] :as query-options}]]\n+  {:pre  [(sequential? query)\n+          ((some-fn map? nil?) query-options)]\n+   :post [(jdbc/valid-jdbc-query? (:results-query %))]}\n+  (let [summary-vec (str/split summarize_by #\",\")\n+        nsummarized (count summary-vec)\n+        aggregate-fn #(assemble-aggregate-sql version query [% query-options])\n+        aggregated-sql-and-params (map aggregate-fn summary-vec)\n+        common-params (second (first aggregated-sql-and-params))","path":"src/puppetlabs/puppetdb/query/aggregate_event_counts.clj","position":59,"original_position":59,"commit_id":"5e256de8223b74636f7751d89f52a9724929327e","original_commit_id":"ef28d65aeae782d0eb1fca22b68eae458e7e4fc4","user":{"login":"mullr","id":24409,"avatar_url":"https://avatars.githubusercontent.com/u/24409?v=3","gravatar_id":"","url":"https://api.github.com/users/mullr","html_url":"https://github.com/mullr","followers_url":"https://api.github.com/users/mullr/followers","following_url":"https://api.github.com/users/mullr/following{/other_user}","gists_url":"https://api.github.com/users/mullr/gists{/gist_id}","starred_url":"https://api.github.com/users/mullr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mullr/subscriptions","organizations_url":"https://api.github.com/users/mullr/orgs","repos_url":"https://api.github.com/users/mullr/repos","events_url":"https://api.github.com/users/mullr/events{/privacy}","received_events_url":"https://api.github.com/users/mullr/received_events","type":"User","site_admin":false},"body":"On a more serious note: how are these params common? I can see how they're the params of the first query, but is it not possible that the other queries have different params?","created_at":"2015-06-16T19:59:18Z","updated_at":"2015-06-16T19:59:18Z","html_url":"https://github.com/puppetlabs/puppetdb/pull/1453#discussion_r32563825","pull_request_url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453","_links":{"self":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/comments/32563825"},"html":{"href":"https://github.com/puppetlabs/puppetdb/pull/1453#discussion_r32563825"},"pull_request":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453"}}},"pull_request":{"url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453","id":37522498,"html_url":"https://github.com/puppetlabs/puppetdb/pull/1453","diff_url":"https://github.com/puppetlabs/puppetdb/pull/1453.diff","patch_url":"https://github.com/puppetlabs/puppetdb/pull/1453.patch","issue_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1453","number":1453,"state":"open","locked":false,"title":"(PDB-1587) Optimize aggregate event counts","user":{"login":"wkalt","id":4952091,"avatar_url":"https://avatars.githubusercontent.com/u/4952091?v=3","gravatar_id":"","url":"https://api.github.com/users/wkalt","html_url":"https://github.com/wkalt","followers_url":"https://api.github.com/users/wkalt/followers","following_url":"https://api.github.com/users/wkalt/following{/other_user}","gists_url":"https://api.github.com/users/wkalt/gists{/gist_id}","starred_url":"https://api.github.com/users/wkalt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wkalt/subscriptions","organizations_url":"https://api.github.com/users/wkalt/orgs","repos_url":"https://api.github.com/users/wkalt/repos","events_url":"https://api.github.com/users/wkalt/events{/privacy}","received_events_url":"https://api.github.com/users/wkalt/received_events","type":"User","site_admin":false},"body":"Author: Wyatt Alt <wyaltster@gmail.com>\r\nDate:   Tue Jun 9 08:33:49 2015 -0700\r\n\r\n    (PDB-1587) Optimize aggregate event counts\r\n    \r\n    This commit improves performance on aggregate-event-counts queries.\r\n    Included are the following changes:\r\n    * new (large) index on resource_events(resource_type, resource_title, timestamp)\r\n    * aggregate-event-counts now supports multiple comma-separated summarize_by params\r\n    * union all is used to combine results from multiple AEC summaries\r\n    * AEC output is now a map array, with a new key containing the summary param\r\n    * CTE used to improve performance when multiple summarize_by values are provided\r\n    * fairly expensive migration merged with mega-migration #29\r\n    * AEC no longer compatible with HSQLDB","created_at":"2015-06-12T00:30:34Z","updated_at":"2015-06-16T19:59:18Z","closed_at":null,"merged_at":null,"merge_commit_sha":"5d54d496cc391b1c006bc98bf7bc2ed62f6ce760","assignee":null,"milestone":null,"commits_url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453/commits","review_comments_url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453/comments","review_comment_url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/comments{/number}","comments_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1453/comments","statuses_url":"https://api.github.com/repos/puppetlabs/puppetdb/statuses/5e256de8223b74636f7751d89f52a9724929327e","head":{"label":"wkalt:ticket/master/pdb-1587-optimize-aggregate-event-counts","ref":"ticket/master/pdb-1587-optimize-aggregate-event-counts","sha":"5e256de8223b74636f7751d89f52a9724929327e","user":{"login":"wkalt","id":4952091,"avatar_url":"https://avatars.githubusercontent.com/u/4952091?v=3","gravatar_id":"","url":"https://api.github.com/users/wkalt","html_url":"https://github.com/wkalt","followers_url":"https://api.github.com/users/wkalt/followers","following_url":"https://api.github.com/users/wkalt/following{/other_user}","gists_url":"https://api.github.com/users/wkalt/gists{/gist_id}","starred_url":"https://api.github.com/users/wkalt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wkalt/subscriptions","organizations_url":"https://api.github.com/users/wkalt/orgs","repos_url":"https://api.github.com/users/wkalt/repos","events_url":"https://api.github.com/users/wkalt/events{/privacy}","received_events_url":"https://api.github.com/users/wkalt/received_events","type":"User","site_admin":false},"repo":{"id":20931903,"name":"puppetdb","full_name":"wkalt/puppetdb","owner":{"login":"wkalt","id":4952091,"avatar_url":"https://avatars.githubusercontent.com/u/4952091?v=3","gravatar_id":"","url":"https://api.github.com/users/wkalt","html_url":"https://github.com/wkalt","followers_url":"https://api.github.com/users/wkalt/followers","following_url":"https://api.github.com/users/wkalt/following{/other_user}","gists_url":"https://api.github.com/users/wkalt/gists{/gist_id}","starred_url":"https://api.github.com/users/wkalt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wkalt/subscriptions","organizations_url":"https://api.github.com/users/wkalt/orgs","repos_url":"https://api.github.com/users/wkalt/repos","events_url":"https://api.github.com/users/wkalt/events{/privacy}","received_events_url":"https://api.github.com/users/wkalt/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/wkalt/puppetdb","description":"Centralized Puppet Storage","fork":true,"url":"https://api.github.com/repos/wkalt/puppetdb","forks_url":"https://api.github.com/repos/wkalt/puppetdb/forks","keys_url":"https://api.github.com/repos/wkalt/puppetdb/keys{/key_id}","collaborators_url":"https://api.github.com/repos/wkalt/puppetdb/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/wkalt/puppetdb/teams","hooks_url":"https://api.github.com/repos/wkalt/puppetdb/hooks","issue_events_url":"https://api.github.com/repos/wkalt/puppetdb/issues/events{/number}","events_url":"https://api.github.com/repos/wkalt/puppetdb/events","assignees_url":"https://api.github.com/repos/wkalt/puppetdb/assignees{/user}","branches_url":"https://api.github.com/repos/wkalt/puppetdb/branches{/branch}","tags_url":"https://api.github.com/repos/wkalt/puppetdb/tags","blobs_url":"https://api.github.com/repos/wkalt/puppetdb/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/wkalt/puppetdb/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/wkalt/puppetdb/git/refs{/sha}","trees_url":"https://api.github.com/repos/wkalt/puppetdb/git/trees{/sha}","statuses_url":"https://api.github.com/repos/wkalt/puppetdb/statuses/{sha}","languages_url":"https://api.github.com/repos/wkalt/puppetdb/languages","stargazers_url":"https://api.github.com/repos/wkalt/puppetdb/stargazers","contributors_url":"https://api.github.com/repos/wkalt/puppetdb/contributors","subscribers_url":"https://api.github.com/repos/wkalt/puppetdb/subscribers","subscription_url":"https://api.github.com/repos/wkalt/puppetdb/subscription","commits_url":"https://api.github.com/repos/wkalt/puppetdb/commits{/sha}","git_commits_url":"https://api.github.com/repos/wkalt/puppetdb/git/commits{/sha}","comments_url":"https://api.github.com/repos/wkalt/puppetdb/comments{/number}","issue_comment_url":"https://api.github.com/repos/wkalt/puppetdb/issues/comments{/number}","contents_url":"https://api.github.com/repos/wkalt/puppetdb/contents/{+path}","compare_url":"https://api.github.com/repos/wkalt/puppetdb/compare/{base}...{head}","merges_url":"https://api.github.com/repos/wkalt/puppetdb/merges","archive_url":"https://api.github.com/repos/wkalt/puppetdb/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/wkalt/puppetdb/downloads","issues_url":"https://api.github.com/repos/wkalt/puppetdb/issues{/number}","pulls_url":"https://api.github.com/repos/wkalt/puppetdb/pulls{/number}","milestones_url":"https://api.github.com/repos/wkalt/puppetdb/milestones{/number}","notifications_url":"https://api.github.com/repos/wkalt/puppetdb/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/wkalt/puppetdb/labels{/name}","releases_url":"https://api.github.com/repos/wkalt/puppetdb/releases{/id}","created_at":"2014-06-17T16:54:41Z","updated_at":"2015-04-03T19:51:15Z","pushed_at":"2015-06-15T22:29:46Z","git_url":"git://github.com/wkalt/puppetdb.git","ssh_url":"git@github.com:wkalt/puppetdb.git","clone_url":"https://github.com/wkalt/puppetdb.git","svn_url":"https://github.com/wkalt/puppetdb","homepage":"http://docs.puppetlabs.com/puppetdb","size":18184,"stargazers_count":0,"watchers_count":0,"language":"Clojure","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"puppetlabs:master","ref":"master","sha":"9a40f4f8b926b491428c7a4f5cab27300681ad17","user":{"login":"puppetlabs","id":234268,"avatar_url":"https://avatars.githubusercontent.com/u/234268?v=3","gravatar_id":"","url":"https://api.github.com/users/puppetlabs","html_url":"https://github.com/puppetlabs","followers_url":"https://api.github.com/users/puppetlabs/followers","following_url":"https://api.github.com/users/puppetlabs/following{/other_user}","gists_url":"https://api.github.com/users/puppetlabs/gists{/gist_id}","starred_url":"https://api.github.com/users/puppetlabs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/puppetlabs/subscriptions","organizations_url":"https://api.github.com/users/puppetlabs/orgs","repos_url":"https://api.github.com/users/puppetlabs/repos","events_url":"https://api.github.com/users/puppetlabs/events{/privacy}","received_events_url":"https://api.github.com/users/puppetlabs/received_events","type":"Organization","site_admin":false},"repo":{"id":2193032,"name":"puppetdb","full_name":"puppetlabs/puppetdb","owner":{"login":"puppetlabs","id":234268,"avatar_url":"https://avatars.githubusercontent.com/u/234268?v=3","gravatar_id":"","url":"https://api.github.com/users/puppetlabs","html_url":"https://github.com/puppetlabs","followers_url":"https://api.github.com/users/puppetlabs/followers","following_url":"https://api.github.com/users/puppetlabs/following{/other_user}","gists_url":"https://api.github.com/users/puppetlabs/gists{/gist_id}","starred_url":"https://api.github.com/users/puppetlabs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/puppetlabs/subscriptions","organizations_url":"https://api.github.com/users/puppetlabs/orgs","repos_url":"https://api.github.com/users/puppetlabs/repos","events_url":"https://api.github.com/users/puppetlabs/events{/privacy}","received_events_url":"https://api.github.com/users/puppetlabs/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/puppetlabs/puppetdb","description":"Centralized Puppet Storage","fork":false,"url":"https://api.github.com/repos/puppetlabs/puppetdb","forks_url":"https://api.github.com/repos/puppetlabs/puppetdb/forks","keys_url":"https://api.github.com/repos/puppetlabs/puppetdb/keys{/key_id}","collaborators_url":"https://api.github.com/repos/puppetlabs/puppetdb/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/puppetlabs/puppetdb/teams","hooks_url":"https://api.github.com/repos/puppetlabs/puppetdb/hooks","issue_events_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/events{/number}","events_url":"https://api.github.com/repos/puppetlabs/puppetdb/events","assignees_url":"https://api.github.com/repos/puppetlabs/puppetdb/assignees{/user}","branches_url":"https://api.github.com/repos/puppetlabs/puppetdb/branches{/branch}","tags_url":"https://api.github.com/repos/puppetlabs/puppetdb/tags","blobs_url":"https://api.github.com/repos/puppetlabs/puppetdb/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/puppetlabs/puppetdb/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/puppetlabs/puppetdb/git/refs{/sha}","trees_url":"https://api.github.com/repos/puppetlabs/puppetdb/git/trees{/sha}","statuses_url":"https://api.github.com/repos/puppetlabs/puppetdb/statuses/{sha}","languages_url":"https://api.github.com/repos/puppetlabs/puppetdb/languages","stargazers_url":"https://api.github.com/repos/puppetlabs/puppetdb/stargazers","contributors_url":"https://api.github.com/repos/puppetlabs/puppetdb/contributors","subscribers_url":"https://api.github.com/repos/puppetlabs/puppetdb/subscribers","subscription_url":"https://api.github.com/repos/puppetlabs/puppetdb/subscription","commits_url":"https://api.github.com/repos/puppetlabs/puppetdb/commits{/sha}","git_commits_url":"https://api.github.com/repos/puppetlabs/puppetdb/git/commits{/sha}","comments_url":"https://api.github.com/repos/puppetlabs/puppetdb/comments{/number}","issue_comment_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues/comments{/number}","contents_url":"https://api.github.com/repos/puppetlabs/puppetdb/contents/{+path}","compare_url":"https://api.github.com/repos/puppetlabs/puppetdb/compare/{base}...{head}","merges_url":"https://api.github.com/repos/puppetlabs/puppetdb/merges","archive_url":"https://api.github.com/repos/puppetlabs/puppetdb/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/puppetlabs/puppetdb/downloads","issues_url":"https://api.github.com/repos/puppetlabs/puppetdb/issues{/number}","pulls_url":"https://api.github.com/repos/puppetlabs/puppetdb/pulls{/number}","milestones_url":"https://api.github.com/repos/puppetlabs/puppetdb/milestones{/number}","notifications_url":"https://api.github.com/repos/puppetlabs/puppetdb/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/puppetlabs/puppetdb/labels{/name}","releases_url":"https://api.github.com/repos/puppetlabs/puppetdb/releases{/id}","created_at":"2011-08-11T18:54:46Z","updated_at":"2015-06-12T01:15:46Z","pushed_at":"2015-06-16T19:36:10Z","git_url":"git://github.com/puppetlabs/puppetdb.git","ssh_url":"git@github.com:puppetlabs/puppetdb.git","clone_url":"https://github.com/puppetlabs/puppetdb.git","svn_url":"https://github.com/puppetlabs/puppetdb","homepage":"http://docs.puppetlabs.com/puppetdb","size":21333,"stargazers_count":163,"watchers_count":163,"language":"Clojure","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":129,"mirror_url":null,"open_issues_count":6,"forks":129,"open_issues":6,"watchers":163,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453"},"html":{"href":"https://github.com/puppetlabs/puppetdb/pull/1453"},"issue":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1453"},"comments":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/issues/1453/comments"},"review_comments":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453/comments"},"review_comment":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/pulls/1453/commits"},"statuses":{"href":"https://api.github.com/repos/puppetlabs/puppetdb/statuses/5e256de8223b74636f7751d89f52a9724929327e"}}}}