This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
72483 1 | |
+-72480 2 | |
+---72549 3 | |
+-----102279 4 | |
+-------72481 5 | |
+---------72668 6 | |
+-72496 2 | |
+-72668 2 | |
+-84940 2 | |
+---72549 3 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
~% ll /usr/local/Cellar/postgresql/9.0.2/share/contrib | |
total 272 | |
-rw-r--r-- 1 agustinvinao staff 10746 Sep 28 2011 _int.sql | |
-rw-r--r-- 1 agustinvinao staff 1548 Sep 28 2011 adminpack.sql | |
-rw-r--r-- 1 agustinvinao staff 5980 Sep 28 2011 dblink.sql | |
-rw-r--r-- 1 agustinvinao staff 1267 Sep 28 2011 fuzzystrmatch.sql | |
-rw-r--r-- 1 agustinvinao staff 12932 Sep 28 2011 hstore.sql | |
-rw-r--r-- 1 agustinvinao staff 742 Sep 28 2011 lo.sql | |
-rw-r--r-- 1 agustinvinao staff 779 Sep 28 2011 pg_buffercache.sql | |
-rw-r--r-- 1 agustinvinao staff 3809 Sep 28 2011 pg_trgm.sql |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT t.*, subs.id FROM connectby('subs', 'sub_id', 'advisor_id', '72483', 0) AS t(sub_id int, advisor_id int, level int), subs WHERE t.sub_id = subs.sub_id AND t.advisor_id = subs.advisor_id | |
results: | |
sub advisor level id | |
72668 72483 1 1680 | |
=> 72480 72483 1 1778 | |
72549 72480 2 1779 | |
102279 72549 3 1951 | |
72481 102279 4 1884 | |
72668 72481 5 1885 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select a_addresses.id, | |
--BOOL_AND( | |
( | |
pt_addresses.id = protected_territories.address_id AND | |
( | |
ACOS( | |
SIN(RADIANS(a_addresses.latitude)) * | |
SIN(RADIANS(pt_addresses.latitude)) + | |
COS(RADIANS(a_addresses.latitude)) * | |
COS(RADIANS(pt_addresses.latitude)) * |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[{"sub_id"=>"72668", "advisor_id"=>"72481", "level"=>"5", "id"=>"1885"}, | |
{"sub_id"=>"72668", "advisor_id"=>"72481", "level"=>"5", "id"=>"1885"}, | |
{"sub_id"=>"72481", "advisor_id"=>"102279", "level"=>"4", "id"=>"1884"}, | |
{"sub_id"=>"72481", "advisor_id"=>"102279", "level"=>"4", "id"=>"1884"}, | |
{"sub_id"=>"102279", "advisor_id"=>"72549", "level"=>"3", "id"=>"1951"}, | |
{"sub_id"=>"102279", "advisor_id"=>"72549", "level"=>"3", "id"=>"1951"}, | |
{"sub_id"=>"72549", "advisor_id"=>"72480", "level"=>"2", "id"=>"1779"}, | |
{"sub_id"=>"72549", "advisor_id"=>"84940", "level"=>"2", "id"=>"1830"}, | |
{"sub_id"=>"92808", "advisor_id"=>"72483", "level"=>"1", "id"=>"1952"}, | |
{"sub_id"=>"72480", "advisor_id"=>"72483", "level"=>"1", "id"=>"1778"}, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<ul> | |
<li> | |
ad1 | |
<ul> | |
<li>sub1</li> | |
</ul> | |
</li> | |
<li> | |
ad2 | |
</li> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
72483 | |
+-72480 | |
+---72549 | |
+-----102279 | |
+-------72481 | |
+---------72668 | |
+-72496 | |
+-72668 | |
+-84940 | |
+---72549 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Target' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS target_premium, | |
COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Single' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS single_premium, | |
COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Excess' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS excess_premium, | |
advisors.id AS entity_id, | |
TRIM(CASE WHEN people.first_name <> ' ' AND people.first_name IS NOT NULL THEN people.first_name || ' ' ELSE '' END::Text || | |
CASE WHEN people.middle_name <> ' ' AND people.middle_name IS NOT NULL THEN people.middle_name || ' ' ELSE '' END::Text || | |
CASE WHEN people.last_name <> ' ' AND people.last_name IS NOT NULL THEN people.last_name || ' ' ELSE '' END::Text || | |
CASE WHEN people.suffix <> ' ' AND people.suffix IS NOT NULL THEN people.suffix ELSE '' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
-- COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Target' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS target_premium, | |
-- COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Single' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS single_premium, | |
-- COALESCE(SUM(CASE submitted_premiums.kind WHEN 'Excess' THEN submitted_premiums.premium * (policy_advisors.commission_percent / 100) ELSE 0.00 END), 0.00) AS excess_premium, | |
COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Target' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS target_premium, | |
COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Single' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS single_premium, | |
COALESCE(SUM(CASE advisor_submitted_premium_totals.kind WHEN 'Excess' THEN advisor_submitted_premium_totals.premium ELSE 0.00 END), 0.00) AS excess_premium, | |
advi |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# And with a simple call like AdvisorSubmittedPremiumTotal.first, these are the results (1s or 1.2s) : | |
>> AdvisorSubmittedPremiumTotal.first | |
AdvisorSubmittedPremiumTotal Load (1206.7ms) SELECT "advisor_submitted_premium_totals".* FROM "advisor_submitted_premium_totals" LIMIT 1 | |
=> #<AdvisorSubmittedPremiumTotal policy_status_id: 1, advisor_id: 46, date: "2008-05-13", kind: "Initial", premium: #<BigDecimal:106f4b1c0,'0.145237E4',18(36)>> | |
# If I filter the date with the current year I have better performance but is slowest than the original sql inside reports (avg 0.5s): | |
>> AdvisorSubmittedPremiumTotal.where("date>=? and date<=?",date_start, date_end).first | |
AdvisorSubmittedPremiumTotal Load (456.5ms) SELECT "advisor_submitted_premium_totals".* FROM "advisor_submitted_premium_totals" WHERE (date>='2012-01-01' and date<='2012-12-31') LIMIT 1 | |
=> #<AdvisorSubmittedPremiumTotal policy_status_id: 1, advisor_id: 54, date: "2012-01-04", kind: "Initial", premium: #<BigDecimal:108c77cd0,'0.975E3',9(36)>> | |
>> Advisor |
OlderNewer