Numeric Manipulation 数学函数

Function Description
ABC() Return a number’s absolute value
COS() Returns the trigonometric cosine of specified angle
EXP() Returns the exponential value of a specific number
PI() Returns the value of PI
SIN() Returns the trigonometric sine of a specified angle
SQRT() Returns the square root of a specified number
TAN() Returns the trigonometric tangent of a specified angle

Text Manipulation 文本函数

字符串截取
字符串计数
字符串转换

Date and Time Manipulation 日期时间函数

  1. SELECT
  2.     from_date,
  3.     YEAR(from_date) AS year,
  4.     MONTH(from_date) AS month,
  5.     DAY(from_date) AS day,
  6.     DATE_FORMAT(from_date, '%d.%m.%Y') AS ru_format
  7. FROM dept_emp
  9. 日期加减
  10. 日期格式化
  11. 日期转换

Using Aggregate Functions 聚合函数

SELECT
    Continent,
    COUNT(*)
FROM country
GROUP BY Continent


SELECT
    Continent,
    AVG(Population)
FROM country
GROUP BY Continent


SELECT
    MAX(Population),
    MIN(Population)
FROM country
WHERE Population > 0


SELECT
     SUM(Population) AS TotalPopulation
FROM country

Aggregates on Distnct Values 去重聚合

SELECT
    COUNT(Continent) AS Continents,
    COUNT(DISTINCT Continent) DistinctContinents
FROM country

Combining Aggregate Funactions 组合使用

SELECT
    COUNT(*) AS CountriesCount,
    MIN(Population) AS MinPopulation,
    MAX(Population) AS MaxPopulation,
    AVG(Population) AS AvgPopulation,
    SUM(Population) AS SumPopulation
FROM country

窗口函数

专用窗口函数
聚合窗口函数

Select device_id,
university,
gpa
From (
Select device_id,
university,
gpa,
row_number() over(partition by university order by gpa) as rk
From user_profile
)a
where rk = 1