数据计算
文章平均质量分 64
数据处理难题、性能优化及解决方案分享
LuckJudy
这个作者很懒,什么都没留下…
展开
-
SQL,在组内根据相邻行修改取值 null 的列
要求:对 company 相同的记录,从前往后处理每组数据,首先删除记录直到遇到第一个 column3 非 NULL 的记录;再把后续等于 NULL 的 column3 改为前面非 NULL 的值,直到遇到下一个非 NULL 的值,然后继续本过程。A2:处理每条记录:同组记录时,如果本行的 column3 非 null 则不修改,为 null 则改为上一条。A3:再选出 column3 非 null 的记录,按 date 排序,返回结果。Java 集成 SPL 可参考。原创 2024-09-13 17:37:09 · 250 阅读 · 0 评论 -
我们需要怎样的 OLAP
近年来流行的敏捷 BI 产品在操作的流畅性和界面的炫丽度都较早期 OLAP 产品有较大的提升,但本质计算功能并没有增长多少,还是在做多维分析,该不能算的还是不能算。业内可能只有 esProc SPL 才是适合 Excel 分析师使用的程序语言了,SPL 有强大的结构化数据处理能力,特别地,SPL 还提供了 Excel 插件,允许用户在 Excel 中直接使用 SPL 代码完成 Excel 很难实现的复杂运算。SPL 功能强大,代码开发效率高,还适合大数据,脚本化的代码也很容易入库管理和再次利用。原创 2024-09-12 15:54:26 · 1091 阅读 · 0 评论 -
SQL,从每组中的 json 字段中提取唯一值
A3:按第 1 列分组并保持原序,然后处理每组数据:按记录顺序累积各项,并去除重复;用本记录的累积结果和下条记录的累积结果求差集。postgreySQL 库表的第 1 列是 ID,用于排序;第 3 列类似 json(非标准 json), 并存在重复项。要求:用 Java 提取每组的唯一值,并使其位于原记录的原位置;或者说,按照原分组每条记录的顺序,自上而下依次删掉重复的项,唯一值保持原位。A2:将每条记录的第 2 个字段拆分为多项。A4:恢复原字符串格式,并返回结果。Java 集成 SPL 可参考。原创 2024-09-12 15:47:28 · 524 阅读 · 0 评论 -
解析主子格式的 csv
某 csv 文件不规范,奇数行为主表记录,一行对应一条记录,有 3 个字段 idUniversity、nameOfUniversity、noOfBuses,其中第 3 个字段是冗余的,用来指明偶数行上子表记录的记录数量。上面 SPL 代码已经建立了主子关联,可以用点号访问子表,比如查询第 2 条主表记录的子表里 idBus 大于 2 的记录,直接写:=A3(4).noOfBuses.select(idBus>2)A3:将每组的第 1 行解析为主表记录,第 2 行解析为多条子表记录,作为主表的字段。原创 2024-09-11 17:08:38 · 391 阅读 · 0 评论 -
在 csv 上增加计算列
要求:用 Java 根据前三列算出第四列 Final Grade,写入新文件,公式为 Lab Exercise * 30% + Long Quiz * 30% + Alternative Assessment * 40%。函数T用来解析或生成文件,derive增加计算列。某 csv 文件存储一些评分参数。Java 集成 SPL 可参考。原创 2024-09-10 15:20:13 · 308 阅读 · 0 评论 -
数据库太慢跑崩的另一罪魁
特别地,如果事实表也可加载进内存,还可以事先关联好,再做 JOIN 时就不必做 HASH 计算和比对了,这些都要利用维表主键参与关联的特征,而不认可这个特征的 SQL 体系就无法实现。分布式时,还可以把维表装入集群节点的内存中,再只要在各节点分别遍历事实表就可以了。主子(同维)表情况就更简单,都按主键有序存储后,可以使用复杂度很低的归并算法,只要一点点内容一次遍历就完成 JOIN,完全不涉及 HASH 分堆和二次 HASH,更没有 shuffle 动作,甚至连 HASH 都不用算,多大数据量都不可能跑崩。原创 2024-09-09 16:45:18 · 1424 阅读 · 0 评论 -
并行处理大 csv 文件
函数cursor用来解析超出内存的大文件,默认单线程,@m表示多线程并行读取,后面的8是并行数,@t表示首行是表头,@c表示分隔符是逗号。要求用 Java 并行处理该文件,选出金额介于 3000 至 5000 美元的订单,按客户分组,统计订单金额和订单数量。Java 集成 SPL 可参考。某 csv 文件存储大量订单。原创 2024-09-05 16:20:56 · 244 阅读 · 0 评论 -
数据库太慢跑崩的一大罪魁
并且要考虑事件发生的次序。对于漏斗这种复杂任务也没问题,数据对 id 有序时,可以每次读入一个 id 的数据进内存,然后可以方便地进行复杂的计算,过程中不会涉及其它 id 的数据,原则上内存只要能装下一个 id 的数据就可以了,同时保存多套 COUNT(DISTINCT) 的计数值也没问题。比如,漏斗统计会有多个搅和在一起的 COUNT(DISTINCT),写出 SQL 会有嵌套的 JOIN,这时候想跑得快,占用的内存就会大得多(JOIN 也会导致内存和性能之间的严重矛盾),跑崩的概率也就会陡增。原创 2024-09-05 14:06:49 · 1570 阅读 · 0 评论 -
比对大 csv 文件
第 1 种比较:找出主键相等且其他字段不相等的记录,先输出主键,再输出 A 的其他字段和 B 的其他字段。函数T可解析或写入csv文件,@c表示用游标读取远超内存的文件,sortx用于游标排序,joinx用于归并关联,merge用于记录归并,@d表示差集。有两个同构的大 csv 文件 A 和 B,主键是 KEY_A、KEY_B、KEY_C,两者有部分数据不同。第 2 种比较:以主键为比较标准,找出 A 和 B 的差集,即 A 有而 B 没有的记录。Java 集成 SPL 可参考。原创 2024-09-04 15:23:52 · 468 阅读 · 0 评论 -
拆开数据库表某列内容后转成字段
用 Java 实现:保留前 3 个字段,把第 4 个字段的所有内容拆分去重,作为第 4-N 个字段名,内容填 0/1 表示本行原来是否包含该值。A3:行转列,其中前 3 列保留,第 4 列的内容转为新字段名,如果包含第 4 列则新内容填 1,否则填 0。查询 BigQuery 数据库的某张表,结果的第 4 个字段是逗号分隔的字符串。A2:将第 4 列拆成多项,每项生成一条记录,~ 表示当前项。A1:通过 JDBC 查询数据库。Java 集成 SPL 可参考。A4:将计算结果写入 csv。原创 2024-09-03 16:23:12 · 249 阅读 · 0 评论 -
万亿秒查是真地吗?比 ORACLE 快 N 倍是不是吹牛?
Oracle 的重点不是分析业务,也就说常说的 AP 业务,它主要是用来做交易的,也就是常说的 TP 业务。如果这些数据总共有 100 列,统计一列时只要读 1% 的数据量,可能也就只有 10G,这样有 10 块硬盘就能在数秒内扫描一遍了,这点配置对于现代服务器集群而言是个小菜,再大 10 倍也不是问题。因为 SQL 已经发展了几十年,成熟的优化算法全世界都知道,也很难有什么新东西,结果就是,那些不掌握这些算法的厂商真能差到没边,而掌握这些算法的厂商好却好不到哪里去,因为你能会我也能会。原创 2024-09-02 15:00:19 · 1122 阅读 · 0 评论 -
找出成员满足条件的整个分组
函数T将文件解析为二维表,group分组并保持每组数据,~表示当前组,count算出符合条件的记录的数量。要求:用 Java 找出至少有一次成绩大于等于 20 的学生的所有记录。某 csv 文件由学生姓名和学生的多次成绩组成。Java 集成 SPL 可参考。原创 2024-09-02 14:56:39 · 148 阅读 · 0 评论 -
三行五行的 SQL 只存在于教科书和培训班
但实际上,这种三行五行的 SQL 只存在于教科书和培训班,我们在现实业务中写的 SQL 不会论行,而是以 K 计的,一条 SQL 几百行 N 层嵌套,写出 3K5K 是常事,这种 SQL,完全谈不上简单易学,对专业程序员都是恶梦。这需要把第一步的分组结果保存起来,而这个中间结果是一个表,其中有个字段要存储对应的分组成员的前 10 名,也就是字段的取值将是个集合,SQL 不支持这种数据类型,还是写不出来。但是我们会发现,即使 SQL 增加了步骤化的 CTE 语法,面对稍复杂的任务时,仍然会写的非常难懂。原创 2024-08-30 13:58:58 · 988 阅读 · 0 评论 -
解析字段值有引号和回车的 csv
函数import将文件解析为二维表,@t表示首行为列名,@c表示csv的分隔符逗号,@o表示处理行中未转义的回车符,@q表示拆掉两端的引号再解析。run可修改并返回修改后的二维表。某 csv 文件的第 3 列是特殊的字符串:两端有引号,被拆分为多行(相当于用回车分隔)。要求:用 Java 整理成规范的二维表。Java 集成 SPL 可参考。原创 2024-08-30 13:56:27 · 282 阅读 · 0 评论 -
比对两个 csv 文件
函数T可解析csv文件,@c表示用游标读取远超内存的文件,sortx用于游标排序,merge用于归并,@d表示求差集。有两个同构的大 csv 文件 A 和 B,主键是 Name、Dept,两者有部分数据不同。请用 Java 比较两个文件的主键,根据主键找到 A 有而 B 没有的记录。Java 集成 SPL 可参考。原创 2024-08-29 14:10:56 · 374 阅读 · 0 评论 -
写着简单和跑得快是一回事,SQL 为什么不可能跑得快?
不过,我们可以设计出低复杂度的算法,也就是计算量更小的算法,计算机执行的动作变少,自然也就会快了。其实,我们知道,只是取前 10 名并不需要将所有数据做大排序,只要在遍历时始终保持一个前 10 名的小集合,遍历过程中不断地修正这个小集合,一次遍历可以了,复杂度是 N*log10,和 log1 亿相比差了 8 倍左右,也就是计算量能小 8 倍,而且这种算法完全不需要外存做缓存。SPL 把 TopN 理解为聚合计算,这个语句中没有排序的字样,也就不会做大排序,而采用刚才说的快速算法了。原创 2024-08-28 14:12:42 · 1242 阅读 · 0 评论 -
Java,统计大 csv 文件中每组的数量
函数T用于解析csv文件,@c表示大文件游标模式。函数groups用于分组汇总。大 csv 文件 data.csv 超出内存,第 3 列是分组列。要求:用 Java 对第 3 列分组,统计每组记录数。Java 集成 SPL 可参考。原创 2024-08-28 14:07:21 · 342 阅读 · 0 评论 -
我们需要怎样的 OLAP
近年来流行的敏捷 BI 产品在操作的流畅性和界面的炫丽度都较早期 OLAP 产品有较大的提升,但本质计算功能并没有增长多少,还是在做多维分析,该不能算的还是不能算。业内可能只有 esProc SPL 才是适合 Excel 分析师使用的程序语言了,SPL 有强大的结构化数据处理能力,特别地,SPL 还提供了 Excel 插件,允许用户在 Excel 中直接使用 SPL 代码完成 Excel 很难实现的复杂运算。SPL 功能强大,代码开发效率高,还适合大数据,脚本化的代码也很容易入库管理和再次利用。原创 2024-08-27 16:36:16 · 1015 阅读 · 0 评论 -
Java, 将 csv 中空值用上一行的值填充
要求:用 Java 修改文件,当 Number 为空时,用上一行的值填充本行,结果存为新 csv。函数T用于读取或写入csv文件,~ 表示当前记录,~[-1]表示上一条记录。csv 文件的 Number 列有时为空。Java 集成 SPL 可参考。原创 2024-08-27 13:30:20 · 164 阅读 · 0 评论 -
列式存储的另一面
但是每块的记录数(就是那个 N)不能太小,否则还是会由于硬盘的最小读取单位而造成较大的浪费,极端情况 N=1 就相当于行存,而且 N 太小也会导致总数据量很大时的目录表很大,目录管理的负担过重。但列存就不一样了,一条记录的每个列各有各的位置,原则上需要都记录下来,这样一来,索引表几乎和原表一样大,空间占用大,读取成本高,这和复制原表后排序区别并不大了。一个数据表有多个列,但运算可能只会用到其中少数几列,采用列存时,用不着的列就不必读出来了,而采用行式存储时,则要把所有列都扫描一遍。遍历用列存,查找用行存。原创 2024-08-26 15:53:49 · 1339 阅读 · 0 评论 -
SQL,给连续的行加上标识序号
A1:SQL取数,按source_id,event_date排序,其中SERIES_ID 为空。A2:不排序,按source_id相邻的值分组,组内按 event_user 相邻的值再分小组。现在要增加计算列SERIES_ID,在source_id内按顺序给每个有序小组加序号。A3:在大组内给每个小组赋予序号,合并各组到记录级。原创 2024-08-23 16:53:53 · 563 阅读 · 0 评论 -
SQL, 有终止条件的多次累计计算
MSSQL数据库的data表存储了多人上电梯的情况,turn表示进电梯的顺序。电梯最大承重1000公斤,每趟能上的人数有限,超重的人要等下一趟。A3:按每趟电梯分组。当累计值大于 1000 时,开始新的分组,并将累计值重置为当前乘客体重。A4:取每组的最后一条记录的 name 字段。请计算每趟电梯最后一个进入的人的名字的列表。A1:用SQL取数,按turn排序。A2:给累计变量设初值。原创 2024-08-22 17:45:31 · 223 阅读 · 0 评论 -
SQL,解析 json
现在要解析json,取出values.values所有的值,即:value1,value2。A2:解析多层json,取出所有的values.value。A1:执行简单SQL,取出json串。原创 2024-08-21 13:51:08 · 381 阅读 · 0 评论 -
SQL,连结多行的字串并去除重复
MSSQL库data表存储了航班衔接关系,ID字段是航班组,即有衔接关系的一组航班;ROUTE字段是前后衔接关系,由前后两个航班组成,用短横连接;LNO字段是衔接顺序。A2:按ID分组,处理每组数据~。先将组内的ROUTE字段按-拆分,再一起合并,对前后重复的航班进行去除重复(不排序去重),最后用-连接。现在要算出每组航班的完整的衔接序列,并按航班组排序,仍然用短横连接。A1:执行简单SQL,注意排序。原创 2024-08-20 14:02:37 · 389 阅读 · 0 评论 -
SQL,将多对多的关联记录按行输出
要求从两张表中找出符合条件W=100,H=500,D=300的记录,先输出Primay里的一行,再输出相关联的Secondary的每一行。A3:A2为空时返回空,A2不为空时,循环A1的每条记录 ~,合并 ~ 和 A2,最后再合并循环的结果。数据库的Primary表和Secondary表有相同的结构,其中W、H、D是主键。A1、A2:执行简单SQL,条件查询。原创 2024-08-19 17:19:40 · 282 阅读 · 0 评论 -
数据库的 IO 到底有多慢?
这里要先计算出总的数据行数,然后再为每个线程拼出 WHERE 条件读取其中一部分数据,这意味着数据库多做了很多计算动作,但读取性能仍然有相当明显的提升,这进一步说明慢主要是慢在接口上,而不是数据库内部的读取和计算慢。事实上,如果用 SQL 针对这个数据表做一次遍历式的聚合运算,返回很小的结果集,就会发现速度也挺快,会比基于文本文件上做同样运算快得多。实测表明,在线程数不多的情况(一般 <10),能达到接近线性提速的效率,也就是有几个读数线程,读数速度就能接近快几倍,实测 6 线程能快出 5 倍。原创 2024-08-16 10:54:51 · 1316 阅读 · 0 评论 -
SQL 的困难源于关系代数
比如股票上涨问题,因为关系代数延用了数学上的无序集合理论,没有给 SQL 造出序的概念,结果就把一个简单问题变成一个困难问题,即使绕路也很难写,于是就发生前面说过的翻译问题解法的难度大于解决问题本身的现象。而且,不幸的是,这个问题是理论层面的,在工程上无论如何优化也无济于事,只能有限改善,不能根除。但是,我们之前分析了一些结构化数据的计算场景,对于写着简单和跑得快这两方面,SQL 其实做得都不好,情况稍一复杂就难以胜任,结果经常导致数千行嵌套 N 层的代码以及几十 G 就要跑几个小时的运算。原创 2024-08-15 13:35:48 · 937 阅读 · 0 评论 -
SQL,计算同行中各列的中位数
函数new新建二维表,~表示当前记录,array将记录的字段值转为序列。原创 2024-08-15 13:28:17 · 224 阅读 · 0 评论 -
SQL,获取 ID 的历史状态
A2:按 Id 分组,根据分组新建二维表。~ 表示当前组,Visit_code[-1] 表示上一条记录的 Visit_code 字段,interval@m 用于求月份的间隔。要求计算出每个病人的历史状态,如果有过咨询,则 Office_Visit=1,否则为 0;A1:通过JDBC从 sas 表取数,按Visit_code,Visit_Date 的顺序排序。原创 2024-08-14 16:41:32 · 475 阅读 · 0 评论