博客 – 木瓜园 -Page 3

为用户提供多个选择的设计，不好

我发现这个公司的产品设计有一个偏好或是成规，倾向于在每个基础操作上为用户提供多种选择，大概是坚信这样的设计能为用户使用提供便利。

这个是一种好设计吗？

我认为这种设计在开发、测试、培训、运维等环节上会额外增加难度，浪费资源。多个选择也意味着出 bug 的几率增加，影响产品稳定性。

至于用户最终怎么选择，取决于用户对产品的认知，取决于哪种选择的资源容易获取，取决于哪种选择的操作简单，取决于哪种选择的可靠性强，等等。可以肯定的是，这事不像一夫多妻制社会轮流过夜那种模式，用户兼顾不了每个选择。

粗暴地为用户提供多个选择的产品，看似是以用户为中心，为用户着想，对用户负责。恰恰，这算是典型的不花时间搞清楚用户真实需求、产品设计不明确的不负责任行为。

给经过两次转置的结果集添加列标题

最近读了 O'Reilly 的技术译文书《SQL经典实例》，原书最后一章有一篇标题为《给经过两次转置的结果集添加列标题》的实例，该实例处理后的数据如下图所示，其结果有点类似电影结尾的职员表，或是以前校园里张贴的成绩排行榜。

SQL 经典实例

作者的想法很棒，同时也觉得作者的实现似乎有些过于复杂和抽象了，不太容易阅读和理解，在此以另一种方法实现，也算是对我自己学习结果的一次检验。（这里的标题仅仅是为匹配原书标题而取的，实际上我并不知道这个场景叫什么比较合适。）

创建数据表：

create table it_app (deptno int NULL,ename VARCHAR(30) NULL);
create table it_sch as select * from it_app where 1<>1;

写入示例数据：

INSERT INTO it_app VALUES (100, 'Clay');
INSERT INTO it_app VALUES (100, 'Mark');
INSERT INTO it_app VALUES (100, 'Jim');
INSERT INTO it_app VALUES (200, 'Lily');
INSERT INTO it_app VALUES (200, 'Lucy');
INSERT INTO it_app VALUES (200, 'Judah');
INSERT INTO it_app VALUES (300, 'Scott');
INSERT INTO it_app VALUES (300, 'Mary');
INSERT INTO it_app VALUES (100, 'Oracle');

INSERT INTO it_sch VALUES (500, 'Kate');
INSERT INTO it_sch VALUES (500, 'Steve');
INSERT INTO it_sch VALUES (500, 'Kettle');
INSERT INTO it_sch VALUES (400, 'Matt');
INSERT INTO it_sch VALUES (400, 'Lary');
INSERT INTO it_sch VALUES (400, 'Danny');

最终的 SQL 实现：

with temp_table as (
-- 整理数据,拼接上部门号, 序号取0(依据 row_number() 从1开始的事实)
select 'app' as mark, deptno, to_char(deptno) as ename, 0 as row_num from it_app
GROUP BY deptno
UNION ALL
select 'app' as mark, deptno,ename,row_number() over(PARTITION by deptno ORDER BY ename) row_num
from it_app
UNION ALL
-- 整理数据, 同上
select 'sch' as mark, deptno, to_char(deptno) as ename, 0 row_num from it_sch
GROUP BY deptno
UNION ALL
select 'sch' as mark, deptno, ename, row_number() over(PARTITION by deptno ORDER BY ename) row_num from it_sch
), tmp_data_src as (
-- 重新进行一次排序编号
select mark, deptno, ename, row_number() over (partition by mark order by deptno,row_num asc) as row_num from temp_table
)
-- 完成转置
select
	max(case mark when 'app' then ename end) as apps,
	max(case mark when 'sch' then ename end) as research
from tmp_data_src
GROUP BY row_num
ORDER BY row_num;

实际上我的这个方案也有它的缺点：较原书的实现方式本实现会各多读一次数据表。

对原书实现方案的改进

原书的实现似乎有 bug: 生成 row_number() 时只用了 id 一个字段，显然还需要加上 ename 字段，否则没法保证多截取的那行跟被替换为 deptno 的那行是同一个员工，下面是我在原作者的基础上做了调整后的实现，简化了层级结构，且实现了按姓名升序排列：

with temp_level as (
	select level as id from dual connect by level < 3 -- 每行需要计算的次数
), temp_table as (
	-- 将最后一行的雇员名称替换为部门号
	select c.mark,c.deptno,c.ename,c.ttl_row,c.row_order,decode(c.row_order, c.ttl_row, TO_CHAR(c.DEPTNO), c.ename) as ename2,
				 row_number() over (partition by c.mark ORDER BY c.mark, c.deptno asc) as last_rownum
	from (
		select a.mark, a.deptno, a.ename, a.ttl_row,b.id,row_number() over (partition by a.DEPTNO order by b.id, a.ename) as row_order 
		from (
			select 'app' as mark, deptno, ename, count(1) over (partition by DEPTNO) as ttl_row --这个字段用于计算行数使用需要再此基础上+1行
			from it_app
			union all
			select 'sch' as mark, deptno, ename, count(1) over (partition by DEPTNO) as ttl_row
			from it_sch
		) a,
		temp_level b
	) c
	where c.row_order > c.ttl_row - 1
)
select
	max(case d.mark when 'app' then ename2 end) as apps,
	max(case d.mark when 'sch' then ename2 end) as research
from temp_table d
GROUP BY d.last_rownum
ORDER BY d.last_rownum

SQL 中处理千分位

实际工作中经常需要对金额类的数值做格式转换，比如有千分位四舍五入保留两位小数，下面介绍下几种常见数据库中这种转化的处理逻辑。

MySQL

SELECT FORMAT(123456789.1234567, 4);
-- 123,456,789.1235

FORMAT() 有三个参数，第一个是要转换的数值，第二个参数是（四舍五入）保留的小数位数，第三个参数用于指定区域设置（将决定转化结果千位分隔符和小数点的格式，缺省为'en US'）。

PostgreSQL

select to_char(123456789.12345, 'FM999,999,999,999.9999');
-- 123,456,789.1235

select to_char(123456789.68, '999,999,999,999.9999');
-- 123,456,789.6800

to_char() 的第一个参数是要转换的数值。
to_char() 的第二个参数中的 FM 用于抑制前导的零或尾随的空白（注意对比上面的两个语句，第二个语句没有 FM，小数位部分以0作了填充补齐4位小数），否则结果可能是一个固定宽度的字符串；.9999表示的是小数部分的位数这里是（四舍五入）保留4位小数，改成.99就是保留两位小数；FM999,999,999,999定义的是千分位格式和最长支持的有效数值长度，将其中的,改成_，结果中的千分位将以_分隔。

Oracle

select to_char(123456789.12345, 'FM999,999,999,999.9999')  from dual;
-- 123,456,789.1235

Oracle 的语法同 PostgreSQL

SQL Server

select convert(VARCHAR, cast(123456789.12345 as money), 1);
-- 123,456,789.12

由于一些历史原因，SQL Server 的底层仍然是 Sybase 的那套逻辑，绝大部分的函数名和语法跟 Sybase 都是通用的，包括这里的千分位转换语法，也就是说把 Sybase 上正常运行的 SQL 语句放在 SQL Server 里也能运行。上述语句包含两个函数，内层的 cast(123456789.12345 as money) 用于将数值转换为 money 格式，外层的 conver() 函数是转化的关键：第一个参数指定转换的目标格式；第二个参数求值 cast(123456789.12345 as money) 的结果是要转换的来源值；第三个参数，用1来指定转换格式为逗号分隔的千分位，同时小数点保留两位小数。
如果你想保留4位小数或者1位小数，对不起不支持，只能自己想办法，下面是一个有千分位且保留4位小数的示例：

select substring(convert(varchar, cast(123456789.12345 as money), 1), 1,
                 charindex('.', convert(varchar, cast(123456789.12345 as money), 0)))
       +
       convert(varchar, round(convert(float, '0.' + substring(convert(varchar, 123456789.12345 * 1.0000), charindex('.', convert(varchar, 123456789.12345 * 1.0000)) + 1, 5)), 4));
-- * 1.0000 是为了兼容整数, 4 为要保留的小数位数

原理是截取利用 SQL Server 原生函数生成千分位，并截取千分位部分及小数点，再拼上小数部分。

PostgreSQL DO 块使用示例

DO $demo_do$ 
DECLARE
	demo_table VARCHAR ( 64 );
	val TEXT;
	sql TEXT;
	err TEXT;
	msg TEXT;
BEGIN
	-- 创建临时表 demo_dept
	demo_table = 'demo_dept';
	sql = 'drop table if EXISTS ' || demo_table || ';';
	sql = sql || 'CREATE temp table ' || demo_table || '(name VARCHAR(20) NULL);';
	raise notice'sql___1(%)', sql;
	EXECUTE ( sql );
	-- 往临时表 demo_dept 写一行数据
	val = 'Tom';
	sql = 'INSERT into ' || demo_table || '(name) VALUES (''' || val || ''')';
	raise notice'sql___2(%)', sql;
	EXECUTE ( sql );
	-- 异常捕获和处理（可选）
	EXCEPTION 
	WHEN OTHERS THEN
		GET stacked DIAGNOSTICS err = RETURNED_SQLSTATE,
		msg = PG_EXCEPTION_DETAIL;
	raise notice'err(%),msg(%)', err, msg;
	-- do something
END;
$demo_do$;

postgresql connectby 函数使用示例

connectby 是 postgresql 的 tablefunc 扩展模块所提供一个函数，它的作用是生成递归形式的记录行。

connectby 的参数

connectby 有7个参数，简单用示例说明如下
select * from connectby ('数据表字', '子字段', '父字段', '排序字段(可选)', '开始行的字段值', '查询深度(0表示不限)', '分支间隔符(可选)')
as t('子字段数据类型', '父字段数据类型', level int/*层级,固定数据类型*/, branch text/* 分支描述,固定数据类型 */, pos int /* 排序序号, 固定数据类型*/)

使用示例

create table prod_cat(cat_id INTEGER, cat_name VARCHAR(40), parent_cat_id INTEGER);

INSERT into prod_cat(cat_id, cat_name, parent_cat_id)
VALUES
(1, 'A', null),
(2, 'AA', 1),
(3, 'AB', 1),
(4, 'AAA', 2),
(5, 'AAB', 4),
(6, 'B', NULL),
(7, 'BA', 6),
(8, 'BAA', 7),
(9, 'BAAA', 8)

select t.*,t1.cat_name,t2.cat_name as parent_cat_name
from connectby('prod_cat', 'cat_id', 'parent_cat_id', 'cat_name', '1', 0, '~')
AS t(cat_id INTEGER, parent_cat_id INTEGER, LEVEL1 INT, branch1 text, se1q integer)
left join prod_cat t1 on t1.cat_id=t.cat_id
left join prod_cat t2 on t2.cat_id=t.parent_cat_id