0%

Posted on Edited on In Disqus:

Ajax: A New Approach to Web Applications

如果最近的交互设计有什么可以称之为“富有魅力的”，那就是产生网页应用了。所有的最酷炫的设计都是在线的。

尽管如此，网页交互设计师还是对咱们制造桌面软件的同事感觉有些许嫉妒。桌面应用程序丰富且拥有响应能力，这似乎远超在web上的相应功能。使得Web快速增长的“类似的简单性”同样也在我们所提供的经验与用户可以从桌面应用中获取的经验之间形成了落差。

这个落差正在慢慢弥合。瞧一下Google Suggest吧。看看你输入的词汇暗示更新的方式，几乎是立即的。现在让我们来看一下Google Maps的放大功能。用你的光标来抓取地图并卷动屏幕。再一次的，一切几乎是瞬间发生的，没有任何页面装载的明显等待时间。

谷歌搜索和谷歌地图是两种新的在自适应路径上我们称之为“AJAX”的网页应用。这个名字是一种缩写，表示了异步的JavaScript以及XML， 而且它表现了一种就网络上可能性而言的基础范式转移。

Ajax不是一种技术。而是多重技术的组合，其中每一种都有各自存在的理由，各自组合后就产生了一种新的强有力的效应。Ajax包含了：

  • 使用XHTML和CSS的基于标准的表现形式
  • 使用文档对象模型DOM的动态交互展示
  • 使用XML 以及XSLT的数据交互操控
  • 应用XMLHttpRequest的异步数据检索

经典的网络应用模型是以下面的方式进行工作的：大多数用户在界面活动，触发一个HTTP请求并返回网络服务器。服务器端做了一些处理：检索数据，分析数值， 与历史遗留系统交互然后返回给客户端一张HTML页面。这是一种较适合网络早期的超文本媒体处理模型，但是使网页适合于超文本的因素并不适用于软件应用。

图一： 网络应用的传统模型（左）与AJAX 模型（右）的对比

这种方式就技术意义而言是一次飞跃，但是就用户体验而言却并没有带来多少提升。当服务器端在做它自己的事的时候，用户在干什么呢？对了，用户在等待。在任务中的每一步，用户都在等待些什么。

显然，当我们设计网络应用的时候，我们不应该让用户傻等。一旦当一个界面被装载时， 为什么用户一定要在每次应用从服务器需求某些资源时等待(halt)呢？事实上，为什么用户一定要看到应用到达服务器的过程呢？

Ajax是如何不同于旧的网络模型
过在用户和服务器之间引入一个Ajax引擎，可以消除Web应用的开始-停止-开始-停止这样的交互过程。初看起来好像在应用中加入了一层反而使反应性降低了，但事实却恰恰相反。

在会话的开始时期，浏览器装载AJAX引擎 ，替代经典模型中“装载一个页面”。这个AJAX引擎是用JavaScript写的并隐藏于框架结构的后面。这个引擎负责翻译用户可见接口并站在用户方与服务器交互。AJAX引擎允许用户以异步的方式进行交互——独立于与服务器的交互。所以用户不再需要盯着沙漏图标或空白windows浏览器以等待服务器来做点什么。

图二： 传统网络应用的异步交互模式（上）与AJAX的异步交互模式(下)的区别

每一个会生成HTTP请求的用户活动通常以JavaScript 的形式调用AJAX引擎。每一个“无需返回服务器端的”用户请求——如简单数据验证，内存中编辑数据以及一些导航——都由AJAX引擎自行处理了。如果引擎需要响应从服务器端返回——如果提交数据用于处理，下载更多的界面数据， 或检索新数据——那么引擎将使用XML来异步这些请求，而无需停止用户与应用的交互。

谁在使用AJAX
谷歌在开发AJAX的应用上已经投入了大量资金。谷歌在去年宣讲的主要产品如Gmail,最终的beta版本的Google Groups,Google Suggest以及Google Maps都是AJAX应用。（欲关注AJAX实施技术点的详解，可以详细的考察谷歌邮件，谷歌搜索以及谷歌地图）

这些项目展示了Ajax并不仅仅是关起门来的技术方向上的研究，而是真是世界中的应用实践。这并不是一个仅仅在实验室中的技术。并且AJAX应用可以从非常简单的单一功能的谷歌搜索到非常复杂且精致的谷歌地图，即AJAX可适合任何尺寸。

在我们自己的网站过去数月中，我们用AJAX来做自己的工作。并且我们意识到我们只是利用了AJAX所能提供的丰富的交互性与反应性中中很表面的一些功能。Ajax是网络应用领域中重要的进展，其重要性正在增长中。并且由于有这么多的开发人员已经了解如何来使用这些技术，我们希望更多的组织在谷歌的技术领导下使用富有竞争力的AJAX。

向前进
在使用AJAX方面最大的挑战不是来自技术的， 核心AJAX技术是成熟的而且易于理解的。 这些应用的设计师的挑战是：忘记我们自以为知道的网络限制，而构想一个更大更丰富的可能性。

Posted on Edited on In Disqus:

复制可以让数据从一个MySQL服务器（主库）复制到一个或多个MySQL服务器（从库）。复制默认是异步的，从库不需要一直连在主库上来接收主库的更新。通过不同配置，你可以复制所有DB、想要的部分DB、或者仅复制DB中选定的表。
在MySQL中使用复制的好处有：

  • 水平扩展————把负载分到多个从库上提高性能。在这种情况下，所有写入和更新在主库上，而读操作可以在一个或多个从库上。这种模型可以提高写的性能（因为主库专门用来更新），同时随着从库数量增多也可以极大提高读的速度。
  • 数据安全性————因为数据被复制到从库，从库能暂停复制，然后进行备份，这样不会破坏主库相关数据
  • 数据分析————实时数据可以在主库上继续生成，而同时我们可以在从库上进行数据分析，不影响主库性能
  • 长距离数据分发————可以使用复制技术，给远程网站访问数据备份，而不用一直连着主库

MySQL 5.7支持不同方式的复制，之前方法是从主库binlog中复制各种事件，需要保证binlog文件及文件中位置在主从间同步。新的方法基于GTID，是事务性的，因此不需要关心这些文件或者文件中的位置，这大大简化了很多复制工作。MySQL使用GTID保证所有主库提交的事务也在从库上执行，从而保证主从一致性。在MySQL 5.7.5及之后版本，GTID记入系统表（mysql.gtid_executed），因此不需要像旧版本依赖binlog来记录。

MySQL中的复制支持不同类型的同步，最初同步是单向、异步复制（复制过程中主库不关心从库是否接收成功、执行成功），在MySQL 5.7中半同步复制被引入，即在一个事务会话中，主库提交会被阻塞，等待至少一个从库告知它收到并记录了该事务。MySQL 5.7也支持延时复制，即从库可以设置自己延迟主库一定时间。如果有同步复制需求，可以使用MySQL Cluster。

有很多方法建立服务器之间的复制，但最好的方法，还是需要看你的数据量、存储引擎类型。
复制log记录格式主要有两种，基于语句的（记录整个SQL语句）和基于行的（只记录改变的行数据），也可以用第三种，混合类型。MySQL 5.7.7之前版本默认用基于语句的，MySQL 5.7.7及之后版本默认用基于行的。

复制功能受很多不同选项、配置参数控制，它们控制了复制的核心操作、超时以及哪些DB和表被选择复制。

你可以用复制来解决很多不同的问题，包括性能、不同DB的备份、系统故障恢复等。

Posted on Edited on In Disqus:

set names影响下面3个字符集

1
2
3
4
5
6
7
mysql> show variables like "character_set%";
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8                                                 |
| character_set_connection | utf8                                                 |
| character_set_results    | utf8
  • character_set_client影响MySQL服务器对client传过来语句的解析处理
  • character_set_connection影响MySQL服务器决定存储数据前是否需要转换编码
  • character_set_results影响MySQL服务器返回的结果编码

总之这3个值就是要一样，还要和客户端一致，所以才有了 set names 这个快捷命令

Posted on Edited on In Disqus:

通过插耳机、打开收音机软件、自动扫描电台，可以得到一系列的频道数据，但频道名称均为“新频道”，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
18,新频道,8870,0
19,新频道,9000,0
20,新频道,9050,0
21,新频道,9080,0
22,新频道,9100,0
23,新频道,9150,0
24,新频道,9190,0
25,新频道,9290,0
26,新频道,9380,0
27,新频道,9450,0
28,新频道,9480,0
29,新频道,9610,0
30,新频道,9660,0
31,新频道,9680,0
32,新频道,9740,0
33,新频道,9930,0
34,新频道,9960,0
35,新频道,10060,0
36,新频道,10180,0
37,新频道,10250,0
38,新频道,10310,0
39,新频道,10390,0
40,新频道,10520,0
41,新频道,10610,0
42,新频道,10660,0
43,新频道,10730,0
44,新频道,8760,0
45,新频道,8800,0
46,呵呵,8810,1

网上找到当地调频频率-频道名称对应数据

整理得到

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
8760 北京人民广播电台文艺广播
8780 天津娱乐调频
8810 河北音乐广播自在调频
8870 中国国际广播电台CRIHITFM
8920 平谷人民广播电台
8960 旅游时尚广播HOTRADIO
9000 中央人民广播电台音乐之声
9050 中国国际广播电台环球资讯广播
9080 河北经济广播电台
9110 天津生活台
9150 中国国际广播电台EASYFM
9190 河北交通台
9200 天津滨海
9250 中央台音乐之声(天津转播)
9280 延庆人民广播电台
9290 顺义人民广播电台
9320 中央台中国之声(唐山转播)
9350 河北音乐广播自在调频（廊坊转播）
9380 承德人民广播电台新闻台
9410 密云人民广播电台
9430 武清人民广播电台
9450 保定燕赵之声
9470 河北文艺城区及东南部地区
9480 河北文艺广播电台
9510 河北廊坊人民广播电台（旅游时尚广播）新闻台
9570 大厂人民广播电台
9600 宝坻广播评书频道
9620 文安人民广播电台
9630 健康100关爱调频
9660 中央人民广播电台经济之声
9720 天津新闻台
9740 北京人民广播电台音乐广播
9800 中央台经济之声(天津转播)
9840 丰润人民广播电台
9860 大兴人民广播电台阳光调频
9860 大兴人民广播电台
9900 天津人民广播电台音乐台
9930 承德（奇经健康百问）
9960 河北固安广播
9980 宁河人民广播电台
10000 关爱调频
10030 廊坊人民广播电台交通长书频道
10060 北京人民广播电台新闻广播
10070 蓟县人民广播电台
10080 张家口新闻
10100 张家口人民广播电台
10110 玉田人民广播电台
10130 怀柔人民广播电台
10140 天津经济台
10180 中央人民广播电台都市之声
10200 唐山交通文艺台
10250 北京人民广播电台体育广播
10260 高碑店人民广播电台
10290 中央台中国之声(天津转播)
10310 昌平人民广播电台
10360 张家口人民广播电台大地之声
10390 北京人民广播电台交通广播
10460 天津文艺台
10520 河北人民广播电台新闻台
10550 香河长书频道
10550 三河人民广播电台
10580 承德交通文艺台
10610 中央人民广播电台中国之声
10660 中央人民广播电台文艺之声
10680 天津人民广播电台交通台
10700 房山人民广播电台
10730 北京人民广播电台城市管理广播
10770 通州人民广播电台京东调频
10800 廊坊人民广播电台戏曲曲艺频道

实际通过sqlite导出后修改再导入的方式处理

1
2
3
4
5
sqlite3 FMRadio.db .dump >radio.sql
cat 1.csv | while read line; do x=`grep ${line% *} radio.sql`;y=${line#* };echo ${x/新频道/$y}; done | sort >tmp.sql
#将tmp.sql替换radio.sql中相关insert语句，实现频道名称的替换
sqlite3 radio.db <radio.sql
mv radio.db FMRadio.db

收音机APP数据文件位置

DB中各表，其中station为电台列表数据

修改后表中数据

再次打开APP得到的电台列表

播放界面，越简单越幸福~

Posted on Edited on In Disqus:

问题来源

单词出现次数统计

  • 计算文本文件words.txt中每个单词的出现次数（按出现次数倒序排列）
  • 假设words.txt只包含小写字母和空格，每个单词只包含小写字母，单词被一个或多个空格分割；每个单词出现次数唯一
1
cat words.txt | tr ' ' '\n' | grep -v "^$" | sort | uniq -c | awk '{print $2,$1}' | sort -nrk2

有效的电话号码

  • 文件file.txt中包含一些电话号码（每行一个），写一个一行的shell脚本输出所有有效的电话号码
  • 假设有效的电话号码只有两种格式：
    (xxx) xxx-xxxx 或 xxx-xxx-xxxx (x代表一个数字)
1
cat file.txt | grep -E "^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$|^[0-9]{3}-[0-9]{3}-[0-9]{4}$"

问题：转置文件内容

  • 给你一个文本文件file.txt，转置它的内容
  • 文件中每行记录列数相同，以空格符分隔
  • 例如，如果file.txt内容如下
    1
    2
    3
    name age
    alice 21
    ryan 30

那么你应该输出

1
2
name alice ryan
age 21 30
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
awk '
{
  for (i=1; i<=NF; i++) {
    if (NR==1) {
      s[i] = $i;
    } else {
      s[i] = s[i]" "$i;
    }
  }
}
END {
  for (i=1; i<=NF; i++) {
    print s[i];
  }
}
' file.txt

输出文件的第十行

  • 如果文件内容不足10行，你应该输出什么？
  • 至少有3种不同方法，尽可能思考所有可能性吧~
1
sed -n "10p" file.txt

Posted on Edited on In Disqus:

问题来源

ps：吐槽下，貌似leetcode升级服务器硬件/软件了，同样的SQL不同时间提交，运行时间截然不同（变快了）。。。

合并两个表

表Person, PersonId为主键

1
2
3
4
5
6
7
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

表Address, AddressId为主键

1
2
3
4
5
6
7
8
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
  • 写sql查询FirstName, LastName, City, State
  • 包含Person中每个人，无论他是否有对应地址
1
2
select p.FirstName,p.LastName,a.City,a.State
from Person p left join Address a on p.PersonId=a.PersonId

TODO：更优解法

工资第二高

表Employee

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
  • 如果没有第二高的工资，则应该 返回null
  • 错误思路：order by Salary desc limit 1,1
1
2
3
4
select max(Salary) from Employee where 
Salary <(
select max(Salary) from Employee
)

工资第n高

表Employee

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
1
2
3
4
select max(Salary) from (
      select t1.Salary from Employee t1 where
      (select count(distinct t2.Salary)=N-1 from Employee t2 where t2.Salary>t1.Salary)
) t

Count+limit+DISTINCT:

1
2
3
4
5
6
SELECT e1.Salary
FROM (SELECT DISTINCT Salary FROM Employee)e1
WHERE (
  SELECT COUNT(Salary) FROM (SELECT DISTINCT Salary FROM Employee)e2 WHERE e1.Salary<e2.Salary
)=N-1
LIMIT 1

分数排名

表Scores

1
2
3
4
5
6
7
8
9
10
+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

结果

1
2
3
4
5
6
7
8
9
10
+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+
  • 排名结果中同样的分数名次应该一样
  • 名次相同记录后面的名次正常累加，不受名次是否相同、相同记录数影响
1
2
3
4
5
6
select t.Score,t.Rank from
(
select t1.Score,(select count(distinct t2.Score) from Scores t2 where t2.Score>=t1.Score) as Rank
from Scores t1
) t
order by t.Score desc

Pre-uniqued:

1
2
3
4
5
SELECT
  Score,
  (SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
FROM Scores
ORDER BY Score desc

找出连续出现3次的数

表Logs

1
2
3
4
5
6
7
8
9
10
11
+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

上面示例中只有“1”满足条件
MySQL用户变量：

1
2
3
4
5
6
7
8
9
select distinct Num from (
    select Rank, min(Num) as Num, count(*) as c from (
        select Num,@curRank := @curRank + IF(@prevNum = Num, 0, 1) AS Rank,@prevNum := Num
        from Logs s,
            (select @curRank:=0) r,
            (select @prevNum:=0) p
        order by Id
    ) t1 group by Rank having c>=3
) t2;

sql自连接：

1
2
3
4
Select DISTINCT l1.Num as ConsecutiveNums
from Logs l1, Logs l2, Logs l3 
where l1.Id=l2.Id-1 and l2.Id=l3.Id-1 
and l1.Num=l2.Num and l2.Num=l3.Num

比老板工资高的员工

表Employee

1
2
3
4
5
6
7
8
+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

返回结果示例

1
2
3
4
5
+----------+
| Employee |
+----------+
| Joe      |
+----------+

子查询，较慢：

1
2
3
select Name from Employee e1
where ManagerId is not null
and Salary>(select max(Salary) from Employee e2 where e2.Id=e1.Managerid)

自连接，更快：

1
2
3
select E1.Name as Employee
from Employee as E1, Employee as E2 
where E1.ManagerId = E2.Id and E1.Salary > E2.Salary

找出重复的邮箱地址

表Person

1
2
3
4
5
6
7
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

返回

1
2
3
4
5
+---------+
| Email   |
+---------+
| a@b.com |
+---------+

使用group by较快：

1
2
3
4
select Email
from Person
group by Email
having count(*) > 1

从不下单的人

表Customers

1
2
3
4
5
6
7
8
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

表Orders

1
2
3
4
5
6
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

示例返回

1
2
3
4
5
6
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

优化版子查询：

1
2
3
SELECT A.Name as Customers
from Customers A
WHERE NOT EXISTS (SELECT 1 FROM Orders B WHERE A.Id = B.CustomerId limit 1)

各部门最高工资

表Employee

1
2
3
4
5
6
7
8
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

表Department

1
2
3
4
5
6
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

示例返回

1
2
3
4
5
6
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

相关子查询：

1
2
3
select d.Name,t.Name,t.Salary
from Department d,(select e1.Name,e1.Salary,e1.DepartmentId from Employee e1,(select DepartmentId,max(Salary) as Salary from Employee group by DepartmentId) e2 where e1.Salary=e2.Salary and e1.DepartmentId=e2.DepartmentId) t
where d.Id=t.DepartmentId

优化版子查询：

1
2
3
4
5
6
7
SELECT D.Name as Department,A.Name as Employee,A.Salary 
FROM 
	Employee A,
	Department D   
WHERE A.DepartmentId = D.Id 
  AND NOT EXISTS 
  (SELECT 1 FROM Employee B WHERE B.Salary > A.Salary AND A.DepartmentId = B.DepartmentId)

各部门前三高工资

表Employee

1
2
3
4
5
6
7
8
9
10
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

表Department

1
2
3
4
5
6
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

示例返回

1
2
3
4
5
6
7
8
9
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

相关子查询：

1
2
3
4
5
select d.Name,e.Name,e.Salary
from Department d,Employee e
where d.Id=e.DepartmentId
and (select count(distinct e1.Salary) from Employee e1 where e1.DepartmentId=e.DepartmentId and e1.Salary>e.Salary) < 3
order by d.Id,e.Salary desc

不使用order by：

1
2
3
4
5
6
7
8
9
select d.Name Department, e1.Name Employee, e1.Salary
from Employee e1 
join Department d
on e1.DepartmentId = d.Id
where 3 > (select count(distinct(e2.Salary)) 
                  from Employee e2 
                  where e2.Salary > e1.Salary 
                  and e1.DepartmentId = e2.DepartmentId
                  );

删除重复邮箱

表Person,主键为Id

1
2
3
4
5
6
7
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

保留最小id记录，示例结果

1
2
3
4
5
6
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Multi-Table Deletes

只有“FROM”前指定的表中的记录被删除

1
2
3
4
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND
p1.Id > p2.Id

找出比前一天温度高的日期

表Weather

1
2
3
4
5
6
7
8
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+
  • TODO 简化问题的话，假设表中日期均连续且顺序与id一一对应，这样条件可以转化为Id比前一个大1

示例结果

1
2
3
4
5
6
+----+
| Id |
+----+
|  2 |
|  4 |
+----+

日期函数并不常用，还可以用上DATEDIFF等

1
2
3
select w1.Id
from Weather w1,Weather w2
where date_sub(w1.`Date`,interval 1 day)=w2.`Date` and w1.Temperature>w2.Temperature

计算退票率

表Trips

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
  • Id为主键
  • Client_Id和Driver_Id都是Users表Users_Id字段的外键
  • Status为ENUM类型，取值为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)

表Users

1
2
3
4
5
6
7
8
9
10
11
12
+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+
  • Users_Id为主键
  • Role为ENUM类型，取值为(‘client’, ‘driver’, ‘partner’)

现在需要查询从2013-10-01到2013-10-03每天未被封禁用户的退票率(四舍五入，保留两位小数)，示例结果

1
2
3
4
5
6
7
+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+
  • 退款状态为cancelled_by_client、cancelled_by_driver
  • 封禁用户Banned为Yes
1
2
3
4
5
select Request_at as Day,round(sum(if(Status in ('cancelled_by_client', 'cancelled_by_driver'),1,0))/count(*),2) as `Cancellation Rate`
from Trips
where Client_Id not in (select Users_Id from Users where Banned='Yes')
and Request_at between '2013-10-01' and '2013-10-03'
group by Request_at;