一周技术总结-20200809

自动可视化的实现思路

MySQL 查询每个分组前 N 个数据

方案一：变量

参考这个文章：

https://blog.csdn.net/qq_33191599/article/details/106188598

以我们这次查询每个业务组最近上传的 15 个 icon 为例：

1
2
3
4
5
6
7
8
9
SELECT id,name, project_id, svg, status, ctime, mtime, views, downloads, author
FROM
(
	SELECT id,name, project_id, svg, status, ctime, mtime, views, downloads, author,
	IF(@tmpproject_id = project_id, @rank:=@rank + 1, @rank:=1) as new_rank,
	@tmpproject_id := project_id AS tmpproject_id
	FROM (SELECT * FROM icon ORDER BY project_id, mtime DESC) tb_a
) tb
WHERE new_rank <= 15;

方案二：子查询

https://blog.csdn.net/cwr452829537/article/details/91972720

1
2
3
4
5
6
7
select i1.* from icon as i1
where (
	select count(*) from icon as i2
	where i1.project_id=i2.project_id
	and i1.mtime < i2.mtime
) < 5
order by project_id asc

sequelize 的 replacements 配置项与 sql 变量冲突的问题

https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-method-query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
const query = `SELECT id,name, project_id, svg, status, ctime, mtime, views, downloads, author
FROM 
(
SELECT id,name, project_id, svg, status, ctime, mtime, views, downloads, author,
IF(@tmpproject_id = project_id, @rank\:=@rank + 1, @rank\:=1) as new_rank,
@tmpproject_id \:= project_id AS tmpproject_id
FROM (SELECT * FROM icon ORDER BY project_id, mtime DESC) tb_a
) tb
WHERE new_rank <= 5
ORDER BY project_id ASC;`;

const data = await app.model.query(query, {
  type: sequelize.QueryTypes.SELECT,
});
return data;

MySQL 重连后，第一次会查询到全部数据

1
2
3
4
5
6
7
8
9
10
SELECT id,name, project_id, svg, status, ctime, mtime, views, downloads, author
      FROM
      (
          SELECT id,name, project_id, svg, status, ctime, mtime, views, downloads, author,
          IF(@tmpproject_id = project_id, @rank:=@rank + 1, @rank:=1) as new_rank,
          @tmpproject_id := project_id AS tmpproject_id
          FROM (SELECT * FROM icon ORDER BY project_id, mtime DESC) tb_a
      ) tb
      WHERE new_rank <= 5
      ORDER BY project_id ASC;

这应该是我 sql 写得有问题，重连后首次查询得到的 new_rank 全部都是 1，所以导致全部被查出来了。

组件生态

FineBI 和 ECharts

position:sticky

吸顶效果

可以配合 top 使用

可能兼容性上要多考虑下