今天午饭过后我去血站献了血,献血的原因很直接,老丈人生病住院需要用血。医院告知血液紧张暂时没血,若想尽快用血,就需要家人朋友去血站献血。

去年11月初家人在另一所医院住院,隔壁床位需要用血的老太太也遇到了缺血的问题,同样医生也是催促家属献血。

上述献血行为是定向的,需要献血人在血站告知和登记自己献血的目的是为哪个医院哪个病区几号床的病人供血,并且,不要求献血人跟用血人的血型一致,哪怕病人是O型血,献血人是B型血,也是可以的,亲属献了血病人就有血液可用了。这里有些事情不能明说,总之,就是这样。

献血前一定要吃饭,献血人最近不能感冒发烧,没生过重大疾病,不曾感染过特定的传染性疾病,年龄在18-55岁之间,体重不低于100斤,前一天没有熬夜喝酒,血站工作人员一一询问确认这些情况,核对献血人的身份信息(要带上身份证)。然后,献血人扫二维码填写个人信息阅读确认献血须知,确认献血量(分别有200毫升、300毫升、400毫升三档可选,不过血站的人会尽量推荐你献300毫升或400毫升,其实坚持选200毫升也是可以的);最后,血站人员会采集献血人的血液现场化验,大约1分钟左右便可知道献血人的血液是否符合标准,符合标准便进入采血环节了。

采血的过程还是比较快的,确认完在哪支胳膊上采血,便安排我坐下。采血操作很简单,工作人员拿过来一套类似医院打吊瓶用的输液设备,其实是一样的,只不过工作方式反了一下:针头扎进血管,血液顺着连接导管流进另一端的储血袋,装满就行了。采血时工作人员提醒说采血的针头比较粗,刚扎进去时会有点疼,建议不要看,我说我不看你扎吧。确实是有点疼的,仔细看那个针头比医院输液用的针头粗了很多,胖一圈都不止。

随后这位工作人员坐旁边问了我一堆问题:在哪上班,休息几天,过年什么时候放假,提醒献完血要多喝水,最近不能剧烈运动高空作业。当然我也问了她一些问题,比如一天有多少人过来献血,比较敏感就不发出来了。没等我将注意力转移到储血袋上,血已经采完了,拔了针按了一会扎针的地方,最后缠上一圈胶带,整个采血环节就结束了。

采完血,工作人员询问有没有不舒服的感觉,让我先喝水休息个15分钟再离开。对了,虽然是定向献血,但也有献血纪念品的,是一只杯子。另外,这个血站虽在闹市人流密集的商场边上,工作人员却只有一个人。从我进门到离开,整个血站总共就工作人员还有我这个献血人两个人,碰巧这几天大降温,外面寒风凛冽,献血屋的空调开的是真足,春天般的温暖,毫不夸张地说这次献血是我有生以来第一次享受这种一对一的公共服务。

关于献血后有没有不适的症状,针对这一问题,我刻意把这篇文章拖了几天再发出来,就是为了观察自己有没有什么不适。总体而言并没有感觉到头晕等不适症状,跟正常时候一样,献完血我就去上班了。献血后的第二天和第三天每晚睡前会有一些畏寒的感觉,即便屋里开了空调也会觉得有些冷,之后就跟往日无异了。

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

这个是一种好设计吗?

我认为这种设计在开发、测试、培训、运维等环节上会额外增加难度,浪费资源。多个选择也意味着出 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

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

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 原生函数生成千分位,并截取千分位部分及小数点,再拼上小数部分。

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$;