Functions
CASE WHEN
Whereas CASE allows you to do different tests with each WHEN clause
CASE. .и
WHEN A = B THEN C
WHEN D != F THEN E
ELSE Q
# use case when in select
SELECT
type,
SUM(CASE WHEN processed THEN 1 ELSE 0 END) :: NUMERIC /
COUNT(*) AS processed_rate
FROM
facebook_complaints. Waral dи,
GROUP BY
type
SELECT count(CASE
WHEN POSITION <= 3
AND has_clicked = 'yes' THEN e.search_id
ELSE NULL
END) * 1.0 / COUNT(r.result_id) * 100 AS percentage
FROM fb_search_results r. 1point 3 acres
LEFT JOIN fb_search_events e ON r.result_id = e.search_id. 1point 3 acres
ORDER BY ASC/ DESC
NOT IN ..
SELECT customers.first_name
FROM customers-baidu 1point3acres
WHERE customers.id NOT IN
(SELECT orders.cust_id
FROM orders
WHERE order_date BETWEEN '2019-02-01' AND '2019-03-01' )
IN.1point3acres
WHERE customers.first_name IN ('Jill', 'Eva')
i.e. use it in subquery:
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT.--
DepartmentId, MAX(Salary)
FROM
Employee. ----
GROUP BY DepartmentId
);
OR
select a.first_name, b.order_date, b.order_details, b.order_cost
from customers a
left join orders b
on a.id = b.cust_id
where a.first_name = 'Jill' or a.first_name = 'Eva'
order by a.id;. From 1point 3acres bbs
TOP-baidu 1point3acres
SELECT TOP(5) artist
FROM artists;
TOP PERCENT
SELECT TOP(5) PERCENT artist
FROM artists; . Χ
NULL
release_year IS NOT NULL;
ISNULL v.s. IFNULL
IFNULL(sum(r.distance), 0)
IFNULL Return Boolean . From 1point 3acres bbs
OFFSET
You can also specify an OFFSET from where to start returning data.
SELECT * FROM artists LIMIT 5 OFFSET 2;
Row Number
row_number() .
. From 1point 3acres bbs
Rank
Rank()
Regex.--
SELECT *
FROM Users
WHERE mail REGEXP '^[a-zA-Z]+[a-zA-Z0-9_.\-]*@leetcode.com$'
DENSE RANK
This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Example:
select Salary, dense_rank() over (order by Salary desc) as rc from Employee;
SELECT Id, DepartmentId, Name AS Employee, Salary, dense_rank () over (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rc FROM Employee;. https://docs.microsoft.com/en-us ... ew=sql-server-ver15
UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.
• Each SELECT statement within UNION must have the same number of columns. 1point3acres.com
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order
. Waral dи,
UNION ALL: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
String manipulation . Waral dи,
substring(order_date, 6, 2). .и
# split_part extract ‘’
count(distinct lower(split_part(business_address, ' ', 2)))
# concat string
concat(upper(left(name, 1)), lower(right(name, length(name)-1)))
# group concat
GROUP_CONCAT(DISTINCT a.PRODUCT ORDER BY a.PRODUCT) AS PRODUCTS
.--
Calculation Operation
ROUND(Duration, 0) AS RoundToZero;
ROUND(Duration, -1) AS RoundToTen;
ROUND(Duration, 0, 1) AS RoundtoTenth
ABS(), SQUARE(), SQRT(), STDEV()
P1.x != p1.y or P2.x != p2.y
(p1.x,p1.y) != (p2.x,p2.y)
power(p1.x - p2.x,2)
. 1point 3 acres
Data Type
# sometime data type is the key
select city, avg(accommodates/beds :: float) as avg_ratio
from airbnb_search_details
where room_type = 'Shared room'
group by city
order by avg_ratio desc;
.
Calculation Date
DATEADD(DATAPART, number, date): . ----
SELECT DATEADD(DD, 30, ‘2020-06-21’)
DATEDIFF(datepart, startdate, enddate):
SELECT OrderDate, ShipDate, -baidu 1point3acres DATEDIFF(D, OrderDate, ShipDate) AS Duration
FROM Shipments
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff;
EXTRACT(month from order_date :: timestamp)=3. Waral dи,
# extract/ substring should only be in select/ careful looking at date or string!
select EXTRACT (YEAR FROM inspection_date :: DATE) AS year,
count(*)
from sf_restaurant_health_violations
where business_name = 'Roxanne Cafe' AND
risk_category IS NOT NULL
group by year
order by year;
Subquery
WITH CTEName (Col1, Col2)
AS. Χ
(
SELECT
FROM
)
Window and Partitions
OVER (PARTITION BY SalesYear ORDER BY SalesYear)
LEAD(CurrentQuota) OVER (PARTITION BY SalesYear ORDER BY ModifiedDate)
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY OrderDate)
RANK() over (partition by a.customer_id order by count(1) desc)
SUM (StudentAge) OVER (ORDER BY Id)
Over clause can be used to select non aggregated values along with Aggregated ones.
Partition BY, ORDER BY inside, and ROWS or RANGE are part of OVER() by clause.
partition by is used to partition data and then perform these window, aggregated functions, and if we don't have partition by the then entire result set is considered as a single partition.
OVER clause can be used with Ranking Functions(Rank, Row_Number, Dense_Rank..), Aggregate Functions like (AVG, Max, Min, SUM...etc) and Analytics Functions like (First_Value, Last_Value, and few others).
Let's See basic syntax of OVER clause
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
PARTITION BY: It is used to partition data and perform operations on groups with the same data.
ORDER BY: It is used to define the logical order of data in Partitions. When we don't specify Partition, entire resultset is considered as a single partition. .и
select id, login_date, lead(login_date, 4) over (partition by id order by login_date) fifth_login
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
UNBOUNDED PRECEDING
Select
item_category as category,
coalesce(SUM(case when date_format(order_date, '%a') = 'Mon' then coalesce(quantity,0) END),0)AS 'MONDAY',
coalesce(SUM(case when date_format(order_date, '%a') = 'Tue' then coalesce(quantity,0) END),0) AS 'TUESDAY',
coalesce(SUM(case when date_format(order_date, '%a') = 'Wed' then coalesce(quantity,0) END),0) AS 'WEDNESDAY',
coalesce(SUM(case when date_format(order_date, '%a') = 'Thu' then coalesce(quantity,0) END),0) AS 'THURSDAY',
coalesce(SUM(case when date_format(order_date, '%a') = 'Fri' then coalesce(quantity,0) END),0) AS 'FRIDAY',.google и
coalesce(SUM(case when date_format(order_date, '%a') = 'Sat' then coalesce(quantity,0) END),0) AS 'SATURDAY',
coalesce(SUM(case when date_format(order_date, '%a') = 'Sun' then coalesce(quantity,0) END),0) AS 'SUNDAY'-baidu 1point3acres
from items i
Left outer join orders o
on o.item_id = i.item_id
group by item_category
order by category https://docs.aws.amazon.com/reds ... ction_synopsis.html
WITH
with EmployeeSalary as(
select Salary, dense_rank() over (order by Salary desc) as rc
from Employee
)
. 1point 3 acresTops and Tricks-baidu 1point3acres
# remember to sort see if we need to include name/ product that has zero records.
# sub query must have alias!!
# user is system defined name
# pay attention to distinct, order by
•
SELECT …
FROM Employee as E1, Department as D
WHERE
(
SELECT Count (Distinct E2.Salary). 1point3acres
FROM Employee as E2
WHERE E1.DepartmentId = E2.DepartmentId and E2.Salary > E1.Salary
) < 3.
AND E1.DepartmentId = D.Id
•
SELECT username, email, COUNT(*). 1point 3 acres
FROM users. 1point 3 acres
GROUP BY username, email
HAVING COUNT(*) > 1
•
select project_id.google и
from project
group by project_id
having count(employee_id) >= all(select count(employee_id) from project group by project_id)
狗仔卡
收藏
淘帖
好苗
杂草
提升卡
置顶卡
关贴卡
开贴卡
变色卡
千斤顶
