数据巧处理:重复数据的合并处理

要处理一批数据,形式为:
lat,lon,num
每个文件有一个timestamp,文件数量巨大,开始我感觉lat、lat应该唯一,但后来检查数据后发现,这批数据存在大量重复的点(可能之前高精度数据是唯一的,但精度降低后导致有重复数据出现),因此需要专门处理。
开始是想在bash里直接处理,但点位相同的数据,其后的num感觉应该sum后替换,因此还没有想到较好的方法。
后来一想数据不是要入库么,入库后先放松唯一性检查,然后通过group操作,可以简单获取到对应的结果。
SQL:
create table sum_point as
select ts,lat,lon,sum(num) from point group by ts,lat,lon

SQL中处理serial类型字段

通常我们在设计数据库时会设计一个自增长的主键(如id、oid),提高数据的查询效率。但有的时候有批量入库的需求,而通常在入库时这个自增长字段是不用赋值的。我们在处理可视化数据入库时(通过CSV数据)时遇到这个问题。
用CSV入库时,若数据的顺序和表中字段的次序一致,则可以这样写SQL:

 insert into some_table values(value1,value2,value3)

 
但若有一个serial字段(假设为第一个字段),则可以这样处理:

 insert into some_table values(default,value1,value2,value3)

 
即用default代表自增长字段的值。

使用SQL分析thunderbird的来往邮件

数据中心的公共服务邮箱已经积累了2万多封邮件(约4.5gb),因此想分析一下这些邮件的信息,比如,收件箱中都有哪些类型的邮件?逐年逐月的邮件分布情况如何?收件箱中的邮件是否存在什么分布规律,包括小时、周、月等?而为了解决这些问题,就需要对这些邮件信息进行分析,首先想当然的是用SQL方法来分析,但通过搜索后发现,科学院的邮箱不支持SQL操作,在WEB管理界面上可以通过高级搜索完成一些对应的搜索功能,但若搜索的邮件数量超过了1000封,则WEB界面也只能返回前1000条记录,因此不能满足我们的实际需求。

而邮件客户端,通常也没有这些功能。在搜索开源的thunderbird邮件客户端后,发现曾经也有人曾经提问过类似的问题:Query Thunderbird Inbox from a SQL database,但当时的说法是不支持。继续搜索后发现thunderbird的目录下存在一个sqlite文件:global-messages-db.sqlite,官方的说法这是一个全局搜索库。

打开后发现基于这个数据库,基本就可以满足我们的需求,主要是对其中两个表的查询:messages和messagesText_content,这两个表的结构如下:
CREATE TABLE messages (id INTEGER PRIMARY KEY, folderID INTEGER, messageKey INTEGER, conversationID INTEGER NOT NULL, date INTEGER, headerMessageID TEXT, deleted INTEGER NOT NULL default 0, jsonAttributes TEXT, notability INTEGER NOT NULL default 0)
CREATE TABLE 'messagesText_content'(docid INTEGER PRIMARY KEY, 'c0body', 'c1subject', 'c2attachmentNames', 'c3author', 'c4recipients')

其中,messages表的id字段和messagesText_content的docid字段应该是一致的(相当于外键)。经过探索,发现messages中的date字段需要先除以1000000,然后再转换为本地时间戳。

使用SQL处理逐年的邮件分布情况就可以这样:
SELECT strftime('%Y',date/1000000,'unixepoch','localtime'),count(id) FROM messages where folderid=206
group by strftime('%Y',date/1000000,'unixepoch','localtime')

postgresql中的sql条件判断

有时候,我们想在sql中使用条件判断来查询一个数据的结果。比如,在wsn的数据申请中,用户可以申请一个site上的所有观测变量,也可以申请其中制定的某些观测变量,在进行查询的时候,我们可能只需要返回用户的申请是全部变量还是非全部变量,这是就需要用到条件判断功能了。

在postgresql中可以case when来进行条件判断:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END;

因此,这个sql语句可以这样来写:

SELECT distinct u.uid,u.site,u.starttime,u.endtime ,s.fld_name,
case when (u.variable=-1) then '-1' else '1' end as variable
from users_pm_down u left join tbl_site_info s on u.site=s.fld_site_id
where starttime is not null and result=-1 and type='down' and uid=100000

postgresql:诡异的left join结果

在修改数据中心后台系统的代码时候,遇到了一个诡异的sql问题,在此记录一下,我还没有找到原因。
fund与mdfund表之间是M:N关系(多对多),其中mdfund记录的是多对多的关系。

系统中需要找出所有符合一定条件的fund记录,以及其与metadata的关系(记录在mdfund表),因此想当然的用是这个SQL代码:

select distinct f.*,mf.id as mfid,mf.fid,mf.place from fund f
left join mdfund mf on f.id=mf.fid
where mf.uuid='b7beb8bf-58d9-4e58-a945-7b6e1dc7705f' and (f.title like '%9112%' or f.fund_id like '%9112%')

这个代码运行出来的结果只有一行记录,将mdfund中有记录的数据给查询出来了,但mdfund中没有记录的数据就没有查询出来,这不是我想要的结果。
尝试换用right join、outer join等,都不是我想要的结果。

因此,决定用个笨点的方法尝试一下,
select * from
(
select distinct f.* from fund f where (f.title like '%9112%' or f.fund_id like '%9112%')
) a
left join
(
select mf.id as mfid,mf.fid,mf.place from mdfund mf where mf.uuid='b7beb8bf-58d9-4e58-a945-7b6e1dc7705f'
) b
on a.id=b.fid

这次出来的结果就是正确的,因此将sql代码优化一下:

select distinct f.*,mf.id as mfid,mf.fid,mf.place from fund f
left join
(
select * from mdfund mf where uuid='b7beb8bf-58d9-4e58-a945-7b6e1dc7705f'
) mf
on f.id=mf.fid
where (f.title like '%9112%' or f.fund_id like '%9112%')

这样的结果是正确的,和第一次尝试的区别就是将uuid限制条件前移了。

这个诡异的差别,为啥能解决问题,我还没有想清楚。

二者的sql解释,正确sql的执行
"HashAggregate (cost=16.97..17.23 rows=26 width=245)"
" -> Hash Right Join (cost=10.08..16.25 rows=26 width=245)"
" Hash Cond: (mf.fid = f.id)"
" -> Bitmap Heap Scan on mdfund mf (cost=4.27..10.42 rows=3 width=12)"
" Recheck Cond: (uuid = 'b7beb8bf-58d9-4e58-a945-7b6e1dc7705f'::uuid)"
" -> Bitmap Index Scan on mdfund_uuid_fid_key (cost=0.00..4.27 rows=3 width=0)"
" Index Cond: (uuid = 'b7beb8bf-58d9-4e58-a945-7b6e1dc7705f'::uuid)"
" -> Hash (cost=5.48..5.48 rows=26 width=233)"
" -> Seq Scan on fund f (cost=0.00..5.48 rows=26 width=233)"
" Filter: ((title ~~ '%9112%'::text) OR ((fund_id)::text ~~ '%9112%'::text))"

错误结果的sql执行:
"HashAggregate (cost=16.28..16.29 rows=1 width=245)"
" -> Hash Join (cost=10.08..16.25 rows=1 width=245)"
" Hash Cond: (mf.fid = f.id)"
" -> Bitmap Heap Scan on mdfund mf (cost=4.27..10.42 rows=3 width=12)"
" Recheck Cond: (uuid = 'b7beb8bf-58d9-4e58-a945-7b6e1dc7705f'::uuid)"
" -> Bitmap Index Scan on mdfund_uuid_fid_key (cost=0.00..4.27 rows=3 width=0)"
" Index Cond: (uuid = 'b7beb8bf-58d9-4e58-a945-7b6e1dc7705f'::uuid)"
" -> Hash (cost=5.48..5.48 rows=26 width=233)"
" -> Seq Scan on fund f (cost=0.00..5.48 rows=26 width=233)"
" Filter: ((title ~~ '%9112%'::text) OR ((fund_id)::text ~~ '%9112%'::text))"

可以看出,唯一的区别就是第二行,正确结果做了一次right join,而错误结果中没有执行right join。
而在sql中直接指定right join同样也不解决问题。

记一次sql的10倍效率提升

在数据中心后台,浏览WATER专题文献的时候,速度明显很慢,感觉哪儿有问题。
开始还以为是SQL中提取了大BLOB字段的问题,后来对比了一下,发现和这个没有关系。

于是尝试在pgadmin中直接修改sql看看效果:

首先用老的代码执行:
select distinct ref.* from mdref r left join reference ref on r.refid=ref.id
left join datasource ds on r.uuid=ds.uuid left join source s on s.id=ds.sourceid
where s.code='water'
ORDER BY ref.year,ref.title

执行下来,平均花费时间大约是460ms。

修改为单表嵌套查询后:
select distinct ref.* from reference ref
where ref.id in (
select r.refid from mdref r left join datasource ds on r.uuid=ds.uuid
left join source s on s.id=ds.sourceid where s.code='water')
ORDER BY ref.year,ref.title;

执行下来,平均花费时间大约是36ms。

这样下来,速度就有10倍以上的提升。

postgresql中将多行记录合并为一个字符串

有时候需要将数据库中两个1:m关系的数据表进行转换为一个独立的表,可以这样处理:

SELECT m.uuid, m.title, m.description, x.data,
 array_to_string(ARRAY( SELECT keyword.keyword FROM keyword
          WHERE keyword.id = m.id), ', '::text) AS keyword
   FROM normalmetadata m
   LEFT JOIN xml x ON m.id = x.id;

这样就可以将其中的多个keyword转换为一个keyword字符串。
在进行全文搜索的时候,就可以建立一个view来代表所需搜索的全文信息。

reference:
http://gotochriswest.com/blog/2011/07/14/postgresql-converting-rows-into-a-string/

简易数据正则处理

因为数据服务填报系统中有个数据需要进行简易的处理,即移除数据列表中的文件大小,而此数据是csv格式的文件,首先想到的就是通过openoffice来进行快捷的处理。
1、OpenOffice尝试
OpenOffice中也有正则支持,默认是关闭的,在“查找与替换”中,点击“更多选项”,然后选择正则表达式,即可打开正则表达式支持。
实际上,我需要运行的正则很简单,即:

\(.*?MB\)

将此内容替换为空即可。
但不知道什么原因,在openoffice中运行此正则不成功,只能查找到部分结果。
2、gedit尝试
gedit也有正则支持的插件,即高级查找替换插件。安装之后运行效果也很好,非常直观。但最后发现对csv全局替换会有误伤,即前面括号中内容也给替换掉了。
3. sql尝试
最终就想在postgresql数据库里直接使用正则进行替换,发现postgresql中有一个regexp_replace函数,可以直接支持sql上进行正则,因此尝试用这个SQL来替换:

select regexp(datalist,'\(.+?MB\)','') from offlineapp

但这个每行只替换了一个第一个后就返回了,因此还需要添加一个flag:

select regexp(datalist,'\(.+?MB\)','',‘g') from offlineapp

wordpress站点更换域名

采用wordpress建站后经常会先使用临时域名,待测试完毕后再更换为正式域名,但在wordpress数据库里对站点域名进行了绑定,因此需要使用数据库进行处理:

update wp_options set option_value=replace(option_value,’old_site_url’,’new_site_url’) where option_value like ‘%old_site_url%’;