SQL
代元培
听风看雨 见贤思齐 抚霜踏雪 青云直上
展开
-
Greenplum Vacuum表的作用
vacuum:该选项主要是清理数据库表中的垃圾空间,该动作会消耗系统一定的资源,引起系统的IO上升,对有一定系统瓶颈来说容易造成堵塞,严重会把GP宕掉,造成数据库瞬断。一般不建议vacuum库中全表,通常做法是vacuum指定的表。原创 2022-01-18 14:23:59 · 436 阅读 · 0 评论 -
即时分析师认证考试题
-- gp考试,执行角色选择:RCC组,集群选择:etron_exam,数据库选择:postgres-- 如果是测试sql,为了速度,可以使用limit限制返回的数据量。-- --用户表:-- create table public.user_info(-- user_id bigint,--用户编号-- user_name text,--用户名-- user_info jsonb,--用户详细信息-- --参考: {user_id:"用户编号",user_name:"用户名",provi.原创 2021-04-22 17:04:50 · 168 阅读 · 0 评论 -
SQL行转列 列转行实现
前言 行列转换在做报表分析时还是经常会遇到的,今天就说一下如何实现行列转换吧。 行列转换就是如下图所示两种展示形式的互相转换。 行转列 PIVOT 后跟一个聚合函数来拿到结果,FOR 后面跟的科目是我们要转换的列,这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。 当然我们也可以用 CASE WHEN 得到同样的结果,就是写起来麻烦一点。使用 CASE WHEN 可以得到和 PIVOT 同样的结果,没有 PIVOT 简...原创 2021-04-21 10:42:17 · 296 阅读 · 0 评论 -
sql优化策略——不必要的union
不必要的union(分析sql逻辑),相似的子查询重复union,仅仅为了过滤不同的条件。 影响:表重复冗余扫描多次,执行效率低 优化方法:使用case when改写union 举例:select id,score,'type1' from table_b where type=1union select id,score,'type2' from table_b where type=2union select id,score,'type3' fr...原创 2021-04-13 14:44:52 · 870 阅读 · 0 评论 -
row_number最简单用法示例
-- row_number 最简单用法select f_uid, f_create_time, f_typefrom( select f_uid, f_create_time, f_type, row_number() over (PARTITION BY f_uid order by f_create_time desc) as rn from open.t_test) tmpwhere rn.原创 2021-04-09 16:42:54 · 145 阅读 · 0 评论 -
SQL删除重复记录
-- 删除f_1,f_2,f_3完全一致重复记录 保留重复记录中f_mt最大的一个create table open.t_ttt( f_1 character varying(128), f_2 character varying(128), f_3 smallint, f_ct timestamp without time zone, f_mt timestamp without time zone) WITH (appendonly=true) DISTRI.原创 2021-03-26 17:58:42 · 73 阅读 · 0 评论 -
json和jsonb类型——PostgreSQL
PostgreSQL支持两种json数据类型:json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json类型存储快,使用慢,jsonb类型存储稍慢,使用较快。注意:键值对的键必须使用双引号从PostgreSQL 9.3开始,json就成了pos原创 2021-03-26 11:38:26 · 1729 阅读 · 0 评论 -
count(*)和count(1)和count(字段)的区别
执行效果上看: count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略字段值为NULL的列; count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略字段值为NULL的列; count(列名)只包括列名那一列,在统计结果的时候,会忽略字段值为值为NULL的列(这里的空不是指空字符串""或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。 执行效率上看: 列名为主键,count(列名)会比count(...原创 2021-03-12 10:07:42 · 234 阅读 · 0 评论 -
近6个月每月都有还款行为用户分析
-- 近6月的数据存放该表create table open.t_ttt_0401( uid bigint) WITH (appendonly=true) DISTRIBUTED BY (uid);-- 这是这一批每个月都有信贷还款的用户insert into open.t_ttt_0401select uidfrom ( select to_char(f_business_time, 'YYYY-MM') as m, f_trans_.原创 2020-10-09 14:39:54 · 292 阅读 · 0 评论 -
PostgreSQL创建分区表
# 创建分区主表CREATE TABLE core.t_trans_paymode_info( f_id bigint, f_trans_id character varying(32), f_enabled smallint, f_pay_mode smallint, f_amount bigint, f_fee_amount bigint, f_fee_rate bigint, f_fee_count_type smallint, .原创 2020-09-11 11:48:16 · 736 阅读 · 0 评论 -
PostgreSQL外部表
-- 方式一create external table ext.r_ext_t_pay (like open.t_pay)location ('gpfdist://*.*.*.*:****/open/t_pay')format 'TEXT' (delimiter as E'\t' null as '\N' escape 'OFF');-- 方式二create external table ext.r_ext_t_pay ( f_id bigint DEFAULT 1 NOT NULL,.原创 2020-09-27 14:06:20 · 866 阅读 · 0 评论 -
PostgreSQL父子表
-- 创建父表CREATE TABLE open.t_log_info( f_date integer, f_action integer, f_uid bigint, f_note character varying(255)) WITH (appendonly=true) DISTRIBUTED BY (f_date, f_action);-- 创建子表 子表自动继承父表的所有字段 通过 INHERITS 指定继承的父表CREATE TABLE open.t.原创 2020-09-27 12:01:16 · 2230 阅读 · 0 评论 -
psql重置自增主键和序列
-- 以表 t_test 为例-- 重置序列起始值为1alter sequence t_test_id_seq restart with 1;-- 查看当前序列SELECT nextval('t_test_id_seq ');-- 清空并重置自增主键TRUNCATE t_test RESTART IDENTITY;-- 清除所有的记录TRUNCATE t_test questions CASCADE;-- 清除所有的记录,并且索引号从0开始TRUNCATE t_test questio.原创 2020-09-24 18:00:16 · 509 阅读 · 0 评论 -
PostgreSQL字符串函数
-- 字符串拼接 PostgreSQLselect 'Post'||'greSQL';-- 返回参数的长度位 24select bit_length('one');-- 返回参数中的字符数 2select char_length('中国');-- 同上 2select length('中国');-- 不同编码下的长度 3select length('中国','GBK');-- 字符串的字节数 6select octet_length('中国');...原创 2020-08-03 10:46:20 · 1646 阅读 · 1 评论 -
psql中character varying和character区别
psql中character varying和character区别 类型 说明 character varying(n), varchar(n) 变长,有长度限制 character(n), char(n) 定长,不足补空白 text 变长,无长度限制 SQL定义了两种基本的字符类型:character varying(n)和character(n),这里的n是一个正整数。两种类型都可以存储最多n个字符的字符串。试图存储更长的字符串到这些类型的字段里会产生..原创 2020-09-23 16:07:37 · 5986 阅读 · 0 评论 -
psql时间/日期函数和操作符
日期/时间操作符 操作符 例子 结果 + date '2001-09-28' + integer '7' date '2001-10-05' + date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00:00' + date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00:00' + interval原创 2020-09-23 15:28:51 · 237 阅读 · 0 评论 -
理财线下转账交易SQL加合计行
# 理财场景 ACS 和 百盈 union all 加合计行 解决排序问题select *from ((select dt as "日期", ACS_DaE_cnt as "ACS_笔数", round(ACSDaE_amount/1000000,2) as "ACS_金额(万元)", concat(round(ACSDaE_zb*100,2),'%') as "ACS_金额占比", round(((daE_b2c+daE_duiSi+daE_kua.原创 2020-09-21 15:41:31 · 152 阅读 · 0 评论 -
内部场景成本率监控报表
select t3.scenario_type as "场景名称", t3.amount as "交易金额", t3.cnt as "订单量", t3.kdj as "客单价", concat(round(t3.cost_r*100,4),'%') as "成本率", concat(round(t4.cost_r*100,4),'%') as "D-2成本率", concat(round((t3.cost_r-t4.cost_r)*100,4),'%.原创 2020-09-10 16:29:30 · 137 阅读 · 0 评论 -
招行理财金额频次分段SQL记录
# 招行理财、金额分段、频次分段select amount_num as "金额分段", cishu as "频次分段", sum(total_amount) as "总交易额", sum(total_chengben) as "总成本", sum(meiren_bishu) as "总笔数", count(F_trans_buyer_user_...原创 2019-12-13 17:29:12 · 354 阅读 · 0 评论 -
SQL实例
# 理财场景 ACS 和 百盈select dt, ACSDaE_cnt, round(ACSDaE_amount/1000000,2) as ACSDaE_amount, concat(round(round(ACSDaE_zb,4)*100,2),'%') as ACSDaE_zb, round(((daE_b2c+daE_duiSi+daE_kuanJie+daE_daiKou+daE_b2b+daE_qiTa)-ACS_DaE_cost)/100,2)...原创 2020-09-02 12:05:33 · 275 阅读 · 0 评论 -
mysqldump导出与导入
# 导出带where条件mysqldump --single-transaction --default-character-set=latin1 -h*.*.*.* -P6000 -udaiyuanpei_r -p****** bfb_db_14 t_trans_query_14_6 --where=" F_seller_user_id=1000000000019146 and F_seller_sub_account_type=1 and F_buyer_user_id like '%146'" &.原创 2020-08-04 14:15:05 · 114 阅读 · 0 评论