{"action":"created","issue":{"url":"https://api.github.com/repos/airbnb/mensor/issues/2","repository_url":"https://api.github.com/repos/airbnb/mensor","labels_url":"https://api.github.com/repos/airbnb/mensor/issues/2/labels{/name}","comments_url":"https://api.github.com/repos/airbnb/mensor/issues/2/comments","events_url":"https://api.github.com/repos/airbnb/mensor/issues/2/events","html_url":"https://github.com/airbnb/mensor/issues/2","id":303704412,"number":2,"title":"Optimise evaluation strategy in circumstances where segmentation is local to each MeasureProvider","user":{"login":"matthewwardrop","id":124910,"avatar_url":"https://avatars3.githubusercontent.com/u/124910?v=4","gravatar_id":"","url":"https://api.github.com/users/matthewwardrop","html_url":"https://github.com/matthewwardrop","followers_url":"https://api.github.com/users/matthewwardrop/followers","following_url":"https://api.github.com/users/matthewwardrop/following{/other_user}","gists_url":"https://api.github.com/users/matthewwardrop/gists{/gist_id}","starred_url":"https://api.github.com/users/matthewwardrop/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/matthewwardrop/subscriptions","organizations_url":"https://api.github.com/users/matthewwardrop/orgs","repos_url":"https://api.github.com/users/matthewwardrop/repos","events_url":"https://api.github.com/users/matthewwardrop/events{/privacy}","received_events_url":"https://api.github.com/users/matthewwardrop/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"matthewwardrop","id":124910,"avatar_url":"https://avatars3.githubusercontent.com/u/124910?v=4","gravatar_id":"","url":"https://api.github.com/users/matthewwardrop","html_url":"https://github.com/matthewwardrop","followers_url":"https://api.github.com/users/matthewwardrop/followers","following_url":"https://api.github.com/users/matthewwardrop/following{/other_user}","gists_url":"https://api.github.com/users/matthewwardrop/gists{/gist_id}","starred_url":"https://api.github.com/users/matthewwardrop/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/matthewwardrop/subscriptions","organizations_url":"https://api.github.com/users/matthewwardrop/orgs","repos_url":"https://api.github.com/users/matthewwardrop/repos","events_url":"https://api.github.com/users/matthewwardrop/events{/privacy}","received_events_url":"https://api.github.com/users/matthewwardrop/received_events","type":"User","site_admin":false},"assignees":[{"login":"matthewwardrop","id":124910,"avatar_url":"https://avatars3.githubusercontent.com/u/124910?v=4","gravatar_id":"","url":"https://api.github.com/users/matthewwardrop","html_url":"https://github.com/matthewwardrop","followers_url":"https://api.github.com/users/matthewwardrop/followers","following_url":"https://api.github.com/users/matthewwardrop/following{/other_user}","gists_url":"https://api.github.com/users/matthewwardrop/gists{/gist_id}","starred_url":"https://api.github.com/users/matthewwardrop/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/matthewwardrop/subscriptions","organizations_url":"https://api.github.com/users/matthewwardrop/orgs","repos_url":"https://api.github.com/users/matthewwardrop/repos","events_url":"https://api.github.com/users/matthewwardrop/events{/privacy}","received_events_url":"https://api.github.com/users/matthewwardrop/received_events","type":"User","site_admin":false}],"milestone":null,"comments":4,"created_at":"2018-03-09T02:41:55Z","updated_at":"2018-05-14T17:10:30Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"Currently the evaluation strategy logic always joins measures in by joining MeasureProviders in by the nominal statistical unit type. In cases where segmentation can be done entirely local to all MeasureProviders, this is inefficient.\r\n\r\nFor example, if you have two measure providers:\r\n- users\r\n  - user [primary key]\r\n  - age [measure]\r\n  - ds [partition, see issue #1]\r\n- signups\r\n  - user [foreign key]\r\n  - lead_time [measure]\r\n  - ds [partition, see issue #1]\r\n\r\nAnd you run:\r\n`m.evaluate('user', measures=['age', 'lead_time'], segment_by=['ds'])\r\n\r\nThe current SQL generated will be something like:\r\n```\r\nSELECT users.ds, SUM(age), SUM(lead_time)\r\nFROM (\r\n    SELECT * FROM users\r\n) users\r\nLEFT JOIN (\r\n    SELECT * FROM signups\r\n) ON users.user = signups.user AND users.ds = signups.ds\r\nGROUP BY users.ds\r\n```\r\nEspecially if the cardinality of users is large, this can be needlessly computationally taxing.\r\n\r\nInstead, we should add an optimisation when all requested segmentation can be performed locally by all measure providers such that the generated SQL looks something like:\r\n```\r\nSELECT a.ds, sum_age, sum_lead_time\r\nFROM (\r\n    SELECT ds, SUM(age) AS sum_age FROM users GROUP BY ds\r\n) a\r\nFULL OUTER JOIN (\r\n    SELECT ds, SUM(lead_time) AS sum_lead_time FROM signups GROUP BY ds\r\n) b ON a.ds = b.ds\r\n```\r\n\r\nWhich potentially reduces the computational complexity of the join substantially in these simple evaluation scenarios.\r\n\r\nThanks for suggesting/flagging this @danfrankj"},"comment":{"url":"https://api.github.com/repos/airbnb/mensor/issues/comments/388892461","html_url":"https://github.com/airbnb/mensor/issues/2#issuecomment-388892461","issue_url":"https://api.github.com/repos/airbnb/mensor/issues/2","id":388892461,"user":{"login":"agorajek","id":371403,"avatar_url":"https://avatars0.githubusercontent.com/u/371403?v=4","gravatar_id":"","url":"https://api.github.com/users/agorajek","html_url":"https://github.com/agorajek","followers_url":"https://api.github.com/users/agorajek/followers","following_url":"https://api.github.com/users/agorajek/following{/other_user}","gists_url":"https://api.github.com/users/agorajek/gists{/gist_id}","starred_url":"https://api.github.com/users/agorajek/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agorajek/subscriptions","organizations_url":"https://api.github.com/users/agorajek/orgs","repos_url":"https://api.github.com/users/agorajek/repos","events_url":"https://api.github.com/users/agorajek/events{/privacy}","received_events_url":"https://api.github.com/users/agorajek/received_events","type":"User","site_admin":false},"created_at":"2018-05-14T17:10:30Z","updated_at":"2018-05-14T17:10:30Z","author_association":"NONE","body":"@matthewwardrop that makes sense. But then my question would be... why are you performing a join between users and signups at all (as shown in the first query)? Is the answer simply \"because that part of the code wasn't optimized yet?\" \r\n\r\nIMO both queries may be valid depending on what question you are asking of the data. Just to clarify, I don't like the first query either, especially the `*` ;) But I am trying to say that the choice between the 1st and the 2nd option may simply be determined by the need of the join on the key between these datasets. And the question of the necessity of such a join should also be inferred from the measures/metrics definitions. And I agree that for the question you asked (`m.evaluate('user', measures=['age', 'lead_time'], segment_by=['ds'])`) a join on the key should not be performed but a single query may still be the desired choice."}}
{"actor":{"display_login":"agorajek"}}