Using CASE and WHEN¶ At times we might have to select values from multiple columns conditionally. We can use CASE and WHEN for that. Let us implement this conditional logic to come up with derived order_status. If order_status is COMPLETE or CLOSED, set COMPLETED If order_status have PENDING in it, then we will say PENDING …
Handling NULL Values¶ Let us understand how to handle nulls. By default if we try to add or concatenate null to another column or expression or literal, it will return null. If we want to replace null with some default value, we can use coalesce. Replace commission_pct with 0 if it is null. coalesce returns …
Data Type Conversion¶ Let us understand how we can type cast to change the data type of extracted value to its original type. In [1]: %load_ext sql In [2]: %env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db In [3]: %%sql SELECT ’09’::int 1 rows affected. Out[3]: int4 9 In [4]: %%sql SELECT current_date AS current_date * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected. Out[4]: current_date 2022-03-14 …
Overview of Numeric Functions¶ Here are some of the numeric functions we might use quite often. abs – always return positive number round – rounds off to specified precision ceil, floor – always return integer. greatest sum, avg min, max random pow, sqrt Some of the functions highlighted are aggregate functions, eg: sum, avg, min, …
Date Manipulation Functions¶ Let us go through some of the important date manipulation functions. Getting Current Date and Timestamp Date Arithmetic using INTERVAL and – operator Getting beginning date or time using date_trunc Extracting information using to_char as well as calendar functions. Dealing with unix timestamp using from_unixtime, to_unix_timestamp Getting Current Date and Timestamp¶ Let …
String Manipulation Functions¶ We use string manipulation functions quite extensively. Here are some of the important functions which we typically use. Case Conversion – lower, upper, initcap Getting size of the column value – length Extracting Data – substr and split_part Trimming and Padding functions – trim, rtrim, ltrim, rpad and lpad Reversing strings – …
