- Postgresql case statement code#
- Postgresql case statement zip#
WHEN (When keyword used to formulate the condition)value_1 THEN(Then keyword used to formulate the condition). 
The below syntax shows simple case expression.
(If the case statement result fails, then execute this statement). (We can use multiple conditions in one case statement). WHEN (When keyword used to formulate the condition)condition_2 THEN(Then keyword used to formulate the condition)result_2 (Result of second case statement). WHEN (When keyword used to formulate the condition) condition_1 THEN(Then keyword used to formulate the condition)result_1 (Result of first case statement). General Case Expressionīelow syntax shows a general case expression. LEFT JOIN MOPS.INDEX_GEN2 on INDIVIDUAL_ID_NUMBER = MOPS.INDEX_GEN2.Hadoop, Data Science, Statistics & others 1. SUM(cast(HOMEOWNER_STATUS = 'U' as int)) *100.0 / count(*) SUM(cast(HOMEOWNER_STATUS = 'R' as int)) *100.0 / count(*) SUM(cast(HOMEOWNER_STATUS = 'P' as int)) *100.0 / count(*) SUM(cast(PRESENCE_OF_ELDERLY_PARENT = 'Y' as int)) *100.0 / count(*) SUM(cast(GENDER = 'F' as int)) *100.0 / count(*) as GENDER_F, The slow part is probably the join, not the aggregation of the results. (I'm not sure how boolean expressions work in Oracle)īut that will probably not speed up the query much. In Postgresql at-least you can write those cases as casts from boolean to int. You'll need to show us your execution plan(s) and table definitions for us to be able to help you further. In Postgresql, you can use the FILTER clause as follows (untested): SELECTĬount(*) filter (where GENDER = 'F') / count(*) * 100 GENDER_F,Ĭount(*) filter (where PRESENCE_OF_ELDERLY_PARENT = 'Y') / count(*) * 100 PRESENCE_ELDERLY_PARENT_Y,Ĭount(*) filter (where HOMEOWNER_STATUS = 'P') / count(*) * 100 HOMEOWNER_STATUS_P,Ĭount(*) filter (where HOMEOWNER_STATUS = 'R') / count(*) * 100 HOMEOWNER_STATUS_R,Ĭount(*) filter (where HOMEOWNER_STATUS = 'U') / count(*) * 100 HOMEOWNER_STATUS_U,Ĭount(*) filter (where MOPS.KBMG_INDEX_GEN2.GEN2 in ('T1','T2','T3','T4','T5','T6','T7')) / count(*) * 100 GEN2_Tīut as others have mentioned, optimising expressions isn't going to help you the Postgresql syntax is merely cleaner. I've tagged both Oracle and postgresql as it currently resides in Oracle but may be migrated to a postgresql instance in the next month. LEFT JOIN MOPS.INDEX_GEN2 on INDIVIDUAL_ID_NUMBER = MOPS.INDEX_GEN2.IID (SUM((CASE WHEN PRESENCE_OF_ELDERLY_PARENT = 'Y'Įlse 0 end)) / count(*)) * 100 PRESENCE_ELDERLY_PARENT_Y,Įlse 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_P,Įlse 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_R,Įlse 0 end)) / count(*)) * 100 HOMEOWNER_STATUS_U, My somewhat unhelpful SQL professor used to say- "just let the query optimizer deal with it - lots of smart people worked on that so you don't have to worry". However, it takes a month of Sundays and I'm wondering if there is some way of optimizing it. In the example below, homeowner status has three values which break out to three CASE statements but in fact it has 8 potential values. In the case of one field there are 35 possible values which generates 35 different CASE statements. Postgresql case statement code#
Now, the below code is actually created by a python script that generates it based on the list of selected factors and the possible values. In the code below, it would give me the percentage of households with an elderly parent.
Postgresql case statement zip#
I have a rather long set of SQL which relies on CASE statements that gives me a percentage of the population of a particular zip code that fits a particular parameter.
0 kommentar(er)
