杭州网站推广营销,免费的crm的app,如何看网站的版本号,东莞财务公司代注册公司1.自定义分组后的分类统计问题#xff08;某组内无数据却仍要展示#xff09;
例题1#xff1a;
查询每个工资类别的银行账户数量。 工资类别如下#xff1a;
Low Salary#xff1a;所有工资 严格低于 20000 美元。Average Salary#xff1a;…1.自定义分组后的分类统计问题某组内无数据却仍要展示
例题1
查询每个工资类别的银行账户数量。 工资类别如下
Low Salary所有工资 严格低于 20000 美元。Average Salary 包含 范围内的所有工资 [$20000, $50000] 。 High Salary所有工资 严格大于 50000 美元。
结果表 必须 包含所有三个类别。 如果某个类别中没有帐户则报告 0。 输入
Accounts 表:
--------------------
| account_id | income |
--------------------
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
--------------------
输出
--------------------------------
| category | accounts_count |
--------------------------------
| Low Salary | 1 |
| Average Salary | 0 |
| High Salary | 3 |
--------------------------------
这里主要难点就是把这类“Average Salary”组内没有符合条件的分组不能将其省略可以通过以下方法用union来操作不是很高级的方法但很完美实现了目的适用于分组比较少的情况真的很实用。
select Low Salary category,count(*) accounts_count from Accounts
where income20000
union
select Average Salary category,count(*) accounts_count from Accounts
where income between 20000 and 50000
union
select High Salary category,count(*) accounts_count from Accounts
where income50000
2. 用union增加不好分组的组的信息
上面是一个例子“Average Salary”这一组由于其没有满足的数据所以我们用group by往往不好对其处理。那就对这组单独处理分组后union到之前正常能分组的结果中。
以下是另一个例子
编写一个解决方案找出在 2019-08-16 时全部产品的最新价格假设所有产品在修改前的价格都是 10 。
示例 1:
输入
Products 表:
------------------------------------
| product_id | new_price | change_date |
------------------------------------
| 1 | 20 | 2019-08-14 |
| 2 | 50 | 2019-08-14 |
| 1 | 30 | 2019-08-15 |
| 1 | 35 | 2019-08-16 |
| 2 | 65 | 2019-08-17 |
| 3 | 20 | 2019-08-18 |
------------------------------------
输出
-------------------
| product_id | price |
-------------------
| 2 | 50 |
| 1 | 35 |
| 3 | 10 |
-------------------
这里product_id为3的产品在‘2019-08-16’前都没出现过不方便加入其他id组的讨论中那就只能将其单独构建除结果内容后用union和返回的其他产品id返回的结果拼接。
本题参考代码如下还用到了row_number构建序号列配合排序和排序倒序为1找极值。 selectproduct_id, new_price as price
from(selectproduct_id,new_price,change_date,(row_number() over (partition by product_id order by change_date DESC)) r
from products
where date(change_date) 2019-08-16) a
where r1unionselectproduct_id, 10 as price
fromProducts
group byproduct_id
havingmin(change_date)2019-08-16;
3. 起累加作用的语句
下面是根据turn列来做累加的语句又用到了over啥的我们之前学习的row_number、lag函数已经提到过了那这里也能不能用partition by呢肯定也是可以的。
SUM(weight) OVER (ORDER BY turn)
举例说明
athlete_idathlete_nameweightturn1John7012Mike7523Sarah6534Linda8045Paul685
SELECT athlete_name,weight,SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
FROM weights
ORDER BY turn;athlete_nameweightcumulative_weightJohn7070Mike75145Sarah65210Linda80290Paul68358
4. 经典例题换座位
示例 1:
输入:
Seat 表:
-------------
| id | student |
-------------
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
-------------
输出:
-------------
| id | student |
-------------
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
-------------
解释:
请注意如果学生人数为奇数则不需要更换最后一名学生的座位。
这题很经典可以单独拿出来作为一个知识点。
我第一次没做出来下面是两种很棒的思路。
1.将偶数 id 减 2 后重排即可
select
rank() over(order by if(id % 2 0,id-2,id)) as id,student
from seat
这不是我写的代码看起来很简洁有效我懂了思路后还要再进一步学习其窗口函数rank()的应用以及窗口函数后面order by加上if配合的使用。这里应该是直接把处理好的id用于窗口函数order by的排序了牛我可能还要再来个中间表啥的。
2.更符合逻辑的思路逻辑非常易懂
SELECT (CASE WHEN MOD(id,2) 1 AND id (SELECT COUNT(*) FROM seat) THEN idWHEN MOD(id,2) 1 THEN id1ElSE id-1END) AS id, student
FROM seat
ORDER BY id;
这里用到case来处理超过两种情况的条件判断。首先明确这题的处理对象最好是id因为id是简单连续数字可以做简单加减运算。
值得注意的是如果最后一个学生序号为奇数那就不要换了。所以要求最多的可能为奇数的最后一个序号优先处理放在case的第一个判断框让它直接等于它本身第二个放不是最后一个序号的技术序号让他序号加1最后只有偶数序号了全部减1完美的逻辑。
5. 注意date_format()里面的参数
终于实践代码的时候用到了date_format()函数特别有用的函数特别是在分类的时候。有几点注意事项是关于参数的。
SELECT DATE_FORMAT(created_at, %Y-%m-%d) AS full_date,DATE_FORMAT(created_at, %Y) AS four_digit_year,DATE_FORMAT(created_at, %y) AS two_digit_year,DATE_FORMAT(created_at, %M) AS full_month_name,DATE_FORMAT(created_at, %m) AS two_digit_month,DATE_FORMAT(created_at, %D) AS day_with_suffix
FROM some_table;结果如下
执行上述查询后的结果如下
full_datefour_digit_yeartwo_digit_yearfull_month_nametwo_digit_monthday_with_suffix2024-10-01202424October101st
解释
最常用的是%Y-%m-%d尤其是%Y-%m常用于具体年月的分组。
6. 单个union语句只能使用一个order by或limit ORDER BY 在 UNION 中的使用 ORDER BY 应该放在整个 UNION 查询的最后而不是每个子查询中。你可以在子查询内部使用 ORDER BY但通常要使用子查询包装。 LIMIT 也需要注意 在 UNION 中你可以对每个子查询使用 LIMIT但不能在 UNION 之后直接再使用单独的 ORDER BY。 举个例子下面如果不加括号就会报错。
(selectu.name results
frommovierating m
order byu.name
limit 1)union all(selectmv.title results
frommovierating m
order bymv.title
limit 1);
加括号明确了每个查询的范围让每个查询都能独立执行自己的 ORDER BY 和 LIMIT然后再用 UNION 合并。不加括号时数据库认为 ORDER BY 和 LIMIT 试图对整个 UNION 操作生效但 SQL 标准不允许这种用法。 值得一提的是这里用到了union all有的时候用的上它不会去掉重复的返回行重复指每一列的数据都一样union会。
--------------------------------------------------------------------------------------------------------------
例题 :
输入
Customer 表:
------------------------------------------------------
| customer_id | name | visited_on | amount |
------------------------------------------------------
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
------------------------------------------------------
输出
--------------------------------------------
| visited_on | amount | average_amount |
--------------------------------------------
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
--------------------------------------------
解释
第一个七天消费平均值从 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 110 120 130 110 140 150)/7 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 120 130 110 140 150 80)/7 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 130 110 140 150 80 110)/7 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 110 140 150 80 110 130 150)/7 142.86做了很久这道题加上gpt的帮忙才得到下面的答案
selectvisited_on,total_amount amount,average_amount
from(SELECT visited_on,SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS total_amount,ROUND(AVG(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW), 2) AS average_amount
FROM (select customer_id,name,visited_on,sum(amount) amount from customer group by visited_on) c
ORDER BY visited_on) c2
where visited_on
IN(select distinct visited_on from customerwhere datediff(visited_on,(select min(visited_on) from customer)) 6);
先介绍一下具体逻辑由里到外
这里某个日期还有多个数据先用groupby用日期分组把相同日期的数据先加起来让每个日期只有一个数据。
然后用sum、avg窗口函数配合range或者rows等下会讲计算从这行开始加上前面6行总共七行的总和和平均值目前得到每一个单独日期的总和和平均值。
最后找出返回的日期从1号开始只有7号开始后的日期才有7天的周期可言。即7号的周期为1号到7号8号的周期为2号到8号......返回7号之后的日期。这里对应着上面代码的where visited_on in7号8号......最终就返回指定日期的总和和平均值这里是7、8、9、10号。
7. 窗口函数的rows、range应用
下面是窗口函数中rows、range的语法及运用
SUM(amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)SUM(amount) OVER (ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)
其中rows那一行起到的作用是配合sum对当前行以及当前行的前面六行总共七行进行求和rows是基于物理行的即只看行数而range则类似配合sum对当前行的日期以及当前行的日期的前六天总共七天的内容进行求和range是基于基于逻辑值的。这里是sum当然也可以配合avg等其他函数使用真的很好用。
8. interval的用法
Interval 在英文里表示间隔、时间段指两个时间或事件之间的距离。在 SQL 中INTERVAL 用来表示日期和时间的时间间隔。它用于增加或减少日期时间以便进行计算。
interval主要的两个常见用法
1. 计算日期用字段加减interval的时间段来定义新的时间
SELECT visited_on - INTERVAL 1 DAY AS previous_day
FROM customer;SELECT *
FROM orders
WHERE order_date NOW() - INTERVAL 30 DAY;2. 配合函数
SELECT DATE_ADD(2024-10-28, INTERVAL 1 YEAR 2 MONTH) AS new_date;
