憨憨呆呆的IT之旅

我见，我思，我行

发表于 本文字数： 1.3k 阅读时长 ≈ 5 分钟

背景

现需要在一台信创虚拟机安装redis 6.x, 未找到二进制包版本，因此需要自行编译安装。
服务器环境如下：

CPU架构系统类别系统发行版本
x86_64LinuxKylin Linux Advanced Server release V10 (Sword)

因为是编译安装，一切都是从源码开始，实际上跟CPU架构和操作系统版本关系不大。下面着重记录一下安装配置过程。

安装redis前的准备工作

1. 预期目标

目标项目标值
安装目录/usr/local/redis
支持TLS是
支持远程访问否
支持免密访问否
集群部署暂不考虑

2. 编译环境准备

主要是准备好gcc编译器。
一般来说麒麟V10已经自带gcc，我机器上gcc版本是7.3.0，版本已经比CentOS 7.x的4.8.5新了，本着够用就好的原则，没有升级到高版本的必要。

3. 准备依赖包

主要是openssl库。如果redis不使用TLS模式的话，本步骤可以跳过。
我机器上已经自带了OpenSSL 1.1.1f版本，只需要额外安装openssl-devel库即可。
偷懒选择yum方式安装。

1
yum -y install openssl-devel

这里简单说明一下，机器是离线的，但事先配置好了本地yum源本地yum源配置方法看这里，所以可以用yum install来安装一些系统镜像里自带的库。

编译安装redis

1. 准备官网源码

先从官网（http://download.redis.io/releases/）下载对应版本源码包。 我下载的是6.2.14版本，下面的记录都以这个版本为准。
上传压缩包到安装目标机器，使用tar xvfz redis-6.2.14.tar.gz解压。
解压后目录为：redis-6.2.14

2. 编译安装

依次执行如下命令进行编译：

1
2
3
4
5
6
7
8
9
10
11
12
# 进入源码目录
cd redis-6.2.14
# 先编译deps子目录下的依赖库
cd deps
make -j4 hiredis lua jemalloc linenoise
cd ..
# 编译（启用TLS支持，这个参数可选）
make -j4 BUILD_TLS=yes
# 测试一下，这个命令执行时间较长
make test
# 安装到自定义目录
make install PREFIX=/usr/local/redis

make过程中可能会出现各种问题，这里就要具体问题具体分析了。一般都是因为缺少lib库，或者没有事先编译deps目录下的依赖库造成的，根据错误提示找找缺少的库文件等等，缺啥补啥，然后make distclean后重新make，大概率能解决。

配置redis服务

设置配置文件

1
2
3
4
5
6
7
8
#在安装目录下创建子目录：
mkdir /usr/local/redis/conf
mkdir /usr/local/redis/data
#从源码目录copy配置文件`redis.conf`到安装目录的`conf`子目录
cd redis-6.2.14
cp redis.conf /usr/local/redis/conf
# 根据预先规划需要，编辑redis.conf
vim redis.conf

主要修改配置项包括：

1
2
3
4
5
6
7
8
9
# 只开启本地监听
bind 127.0.0.1 -::1
protected-mode yes
port 6379
daemonize yes
pidfile /var/run/redis_6379.pid
logfile ""
dir /usr/local/redis/data
requirepass "密码"

使用cat /usr/local/redis/conf/redis.conf |grep -v "^#"|grep -v "^$"命令得到完整有效的配置项如下（已过滤注释），供参考：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
bind 127.0.0.1 -::1
protected-mode yes
port 6379
tcp-backlog 511
timeout 0
tcp-keepalive 300
daemonize yes
pidfile /var/run/redis_6379.pid
loglevel notice
logfile ""
databases 16
always-show-logo no
set-proc-title yes
proc-title-template "{title} {listen-addr} {server-mode}"
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb
rdb-del-sync-files no
dir /usr/local/redis/data
replica-serve-stale-data yes
replica-read-only yes
repl-diskless-sync no
repl-diskless-sync-delay 5
repl-diskless-load disabled
repl-disable-tcp-nodelay no
replica-priority 100
acllog-max-len 128
requirepass "jgDWw**RxRG&%#J3"
lazyfree-lazy-eviction no
lazyfree-lazy-expire no
lazyfree-lazy-server-del no
replica-lazy-flush no
lazyfree-lazy-user-del no
lazyfree-lazy-user-flush no
oom-score-adj no
oom-score-adj-values 0 200 800
disable-thp yes
appendonly no
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes
aof-use-rdb-preamble yes
lua-time-limit 5000
slowlog-log-slower-than 10000
slowlog-max-len 128
latency-monitor-threshold 0
notify-keyspace-events ""
hash-max-ziplist-entries 512
hash-max-ziplist-value 64
list-max-ziplist-size -2
list-compress-depth 0
set-max-intset-entries 512
zset-max-ziplist-entries 128
zset-max-ziplist-value 64
hll-sparse-max-bytes 3000
stream-node-max-bytes 4096
stream-node-max-entries 100
activerehashing yes
client-output-buffer-limit normal 0 0 0
client-output-buffer-limit replica 256mb 64mb 60
client-output-buffer-limit pubsub 32mb 8mb 60
hz 10
dynamic-hz yes
aof-rewrite-incremental-fsync yes
rdb-save-incremental-fsync yes
jemalloc-bg-thread yes

配置开机启动

新建或编辑一个启动配置文件：vim /lib/systemd/system/redis.service
文件内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
[Unit]
Description=Redis
After=network.target
[Service]
Type=forking
PIDFile=/var/run/redis_6379.pid
ExecStart=/usr/local/redis/bin/redis-server /usr/local/redis/conf/redis.conf
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s QUIT $MAINPID
PrivateTmp=true
[Install]
WantedBy=multi-user.target

然后执行：

1
2
3
4
systemctl daemon-reload
systemctl enable redis
systemctl start redis
systemctl status redis

测试验证

下面列一下我用来验证redis的命令, 供参考：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 打开命令行客户端
cd /usr/local/redis
bin/redis-cli
# 输入密码完成认证
auth "密码"
# 选择db 0
select 0
# 设置键值对 a:10
set a 10
# 获取所有key
keys *
# 获取a的值
get a
# 删除键值对a
del a
# 获取所有key
keys *
# 退出客户端
quit

发表于 本文字数： 270 阅读时长 ≈ 1 分钟

我拿到的机器是一台Linux内网机，操作系统是麒麟V10, 由于需要自己在无网环境下安装一些软件，这些软件都或多或少依赖一些系统基础包，因此需要配置本地yum源。记录配置过程如下：

获取系统ios镜像文件

这个需要自行获取。

挂载ios镜像

我是让同事帮忙挂载的，大体步骤如下

1
2
3
4
5
6
7
8
# 先挂载ios文件
mount /root/upload/Kylin-Server-10-SP2-aarch64-Release-Build09-20210524.iso /run/media/root/Kylin-Server-10
# 再挂载cdrom设备
mount /dev/sr0 /run/media/root/Kylin-Server-10
# 查看挂载点
df -h
# 可以像普通目录一样查看ios文件内容
ls /run/media/root/Kylin-Server-10

备份yum源

1
2
3
cd /etc/yum.repo.d/
mkdir backup
mv *.repo backup

配置本地源

创建xxx.repo文件并设置内容：

1
2
3
4
5
6
7
8
9
10
cat > /etc/yum.repos.d/local.repo << EOF 
###Kylin Linux Advanced Server 10 - os repo###

[ks10-adv-os]
name = Kylin Linux Advanced Server 10 - Os
baseurl = file:///run/media/root/Kylin-Server-10
gpgcheck = 1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-kylin
enabled = 1
EOF

更新yum源：

1
2
3
yum clean all
yum makecache
yum list

完毕！

发表于 本文字数： 261 阅读时长 ≈ 1 分钟

记录一下maven常用命令，免得需要时到处乱找。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mvn -v //查看版本
mvn archetype:create //创建 Maven 项目
mvn compile //编译源代码
mvn test-compile //编译测试代码
mvn test //运行应用程序中的单元测试
mvn site //生成项目相关信息的网站
mvn package //依据项目生成 jar 文件
mvn install //在本地 Repository 中安装 jar
mvn -Dmaven.test.skip=true //忽略测试文档编译
mvn clean //清除目标目录中的生成结果
mvn clean compile //将.java类编译为.class文件
mvn clean package //进行打包
mvn clean test //执行单元测试
mvn clean deploy //部署到版本仓库
mvn clean install //使其他项目使用这个jar,会安装到maven本地仓库中
mvn archetype:generate //创建项目架构
mvn dependency:list //查看已解析依赖
mvn dependency:tree //看到依赖树
mvn dependency:analyze //查看依赖的工具
mvn help:system //从中央仓库下载文件至本地仓库
mvn help:active-profiles //查看当前激活的profiles
mvn help:all-profiles //查看所有profiles
mvn help:effective -pom //查看完整的pom信息

发表于 本文字数： 168 阅读时长 ≈ 1 分钟

最近老是碰到需要确认linux发行版本的需求，自己记不住，每次都去查bing，查烦了。特意搜集整理、记录一下。
总的来说，以下这么几种方法搭配使用，效果更佳。

  • cat /etc/*-release – 感觉这个最靠谱，红帽的、ubuntu的甚至国产的发行版，一般能查出来。
  • cat /etc/issue – 有时候啥有用信息也没有，查了个寂寞。
  • lsb_release – 不少发行版不支持，聊胜于无吧。
  • uname -a – 能看到一些信息吧，比如是 x86_64 架构的，甚至发行版名称也有。
  • cat /proc/version – 感觉跟uname差不多。
  • dmesg | grep "Linux" – 难记，不常用

发表于 本文字数： 175 阅读时长 ≈ 1 分钟

平时习惯针对整个DataFrame把nan替换成0，用df.fillna(0, inplace=True)就OK了。今天突然有人问，如何只替换某一列的nan。测试并记录如下。
构建测试DataFrame，由3列组成 name、age、score, 后两列都有nan值。接下来测试如何只处理age列。

1
2
3
4
df = pd.DataFrame({'name':['aby','boy','cilia'],'age':[18,np.nan,20],'score':[9.5,8.0,np.nan]})
print(df)
df['age'].fillna(0, inplace=True)
print(df)

处理前，DataFrame如下：

1
2
3
4
    name   age  score
0    aby  18.0    9.5
1    boy   NaN    8.0
2  cilia  20.0    NaN

处理后，DataFrame如下：

1
2
3
4
    name   age  score
0    aby  18.0    9.5
1    boy   0.0    8.0
2  cilia  20.0    NaN

以上示例代码表明，可直接用fillna()方法针对某一列DataSeries做替换，参数同DataFrame一样。

发表于 更新于 本文字数： 2.1k 阅读时长 ≈ 7 分钟

窗口函数简介

窗口函数主要是用来对表的数值字段做统计分析的。既然是统计分析就必然涉及到多条记录。
窗口函数一般配合avg、max等聚合函数一起使用，通过跨越多行记录的统计数据来影响当前行。
它们跟普通聚合函数最大的不同在于它们不改变结果集记录行数。
窗口函数的调用语法类似：

1
function_name(expression) over(window_name)

function_name可以为max,min,avg,sum,count等普通聚合函数，也可以为rank,dense_rank等排名函数。
over子句确定了窗口划分方法，也就是我们通常意义上说的分组,类似group by。

窗口函数注意事项

执行顺序

窗口函数在GROUP BY、HAVING、WHERE子句以及聚合函数之后、排序子句之前执行。因此只允许出现在查询的SELECT和ORDER BY子句中。

窗口函数案例学习

下面用一些实际的使用场景来说明窗口函数的作用。

数据准备

先准备一些测试数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- postgresql创建自增序列和表结构
CREATE SEQUENCE employee_empno_seq START 1;
CREATE TABLE employee_tbl (
  empno int4 NOT NULL DEFAULT nextval('employee_empno_seq'::regclass),
  empname varchar(100) COLLATE "pg_catalog"."default",
  deptname varchar(100) COLLATE "pg_catalog"."default",
  salary int4,
  PRIMARY KEY ("empno")
);
-- 准备测试数据
INSERT INTO employee_tbl(empno,deptname,empname,salary) VALUES
(1,'sales','s1',5000),
(2,'personnel','p1',3900),
(3,'sales','s2',4800),
(4,'sales','s3',5000),
(5,'personnel','p2',3500),
(6,'personnel','p3',4100),
(7,'develop','d1',4200),
(8,'develop','d2',6000),
(9,'develop','d3',4500),
(10,'develop','d4',5200),
(11,'develop','d5',5200);

准备好的表数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary
-------+---------+-----------+--------
     1 | s1      | sales     |   5000
     2 | p1      | personnel |   3900
     3 | s2      | sales     |   4800
     4 | s3      | sales     |   5000
     5 | p2      | personnel |   3500
     6 | p3      | personnel |   4100
     7 | d1      | develop   |   4200
     8 | d2      | develop   |   6000
     9 | d3      | develop   |   4500
    10 | d4      | develop   |   5200
    11 | d5      | develop   |   5200
(11 rows)

统计员工工资和所在部门平均工资的差异百分比

使用窗口函数计算各部门平均工资：

1
2
3
SELECT empno,empname,deptname,salary,(avg(salary) over(partition by deptname)) as avg_salary
FROM employee_tbl
order by empno asc;

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary |      avg_salary
-------+---------+-----------+--------+-----------------------
     1 | s1      | sales     |   5000 | 4933.3333333333333333
     2 | p1      | personnel |   3900 | 3833.3333333333333333
     3 | s2      | sales     |   4800 | 4933.3333333333333333
     4 | s3      | sales     |   5000 | 4933.3333333333333333
     5 | p2      | personnel |   3500 | 3833.3333333333333333
     6 | p3      | personnel |   4100 | 3833.3333333333333333
     7 | d1      | develop   |   4200 | 5020.0000000000000000
     8 | d2      | develop   |   6000 | 5020.0000000000000000
     9 | d3      | develop   |   4500 | 5020.0000000000000000
    10 | d4      | develop   |   5200 | 5020.0000000000000000
    11 | d5      | develop   |   5200 | 5020.0000000000000000
(11 rows)

对上述sql美化一下,把平均工资取整，再加上员工工资跟部门平均工资的差异比例（这里暂不考虑性能调优啥的）：

1
2
3
SELECT empno,empname,deptname,salary,(round(avg(salary) over(partition by deptname))) as avg_salary, round(((salary/(avg(salary) over(partition by deptname)))-1), 2) as diff_ratio 
FROM employee_tbl
order by empno asc;

最终得到结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | avg_salary | diff_ratio
-------+---------+-----------+--------+------------+------------
     1 | s1      | sales     |   5000 |       4933 |       0.01
     2 | p1      | personnel |   3900 |       3833 |       0.02
     3 | s2      | sales     |   4800 |       4933 |      -0.03
     4 | s3      | sales     |   5000 |       4933 |       0.01
     5 | p2      | personnel |   3500 |       3833 |      -0.09
     6 | p3      | personnel |   4100 |       3833 |       0.07
     7 | d1      | develop   |   4200 |       5020 |      -0.16
     8 | d2      | develop   |   6000 |       5020 |       0.20
     9 | d3      | develop   |   4500 |       5020 |      -0.10
    10 | d4      | develop   |   5200 |       5020 |       0.04
    11 | d5      | develop   |   5200 |       5020 |       0.04
(11 rows)

这样我们就能很直观的看到部门内每个员工工资相对平均工资的偏离情况了。
如果窗口函数的over子句括号内容为空，则表示把所有结果集作为一个分组来处理。例如，以下SQL可以在员工工资记录后追加一列“公司平均工资”：

1
2
3
SELECT empno,empname,deptname,salary,round(avg(salary) over()) as avg_salary
FROM employee_tbl
order by empno asc;

得到结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | avg_salary
-------+---------+-----------+--------+------------
     1 | s1      | sales     |   5000 |       4673
     2 | p1      | personnel |   3900 |       4673
     3 | s2      | sales     |   4800 |       4673
     4 | s3      | sales     |   5000 |       4673
     5 | p2      | personnel |   3500 |       4673
     6 | p3      | personnel |   4100 |       4673
     7 | d1      | develop   |   4200 |       4673
     8 | d2      | develop   |   6000 |       4673
     9 | d3      | develop   |   4500 |       4673
    10 | d4      | develop   |   5200 |       4673
    11 | d5      | develop   |   5200 |       4673
(11 rows)

按员工工资由高到低排名

先在全公司排名：

1
select empno, empname, deptname, salary, rank() over(order by salary desc) from employee_tbl;

得到结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | rank
-------+---------+-----------+--------+------
     8 | d2      | develop   |   6000 |    1
    11 | d5      | develop   |   5200 |    2
    10 | d4      | develop   |   5200 |    2
     1 | s1      | sales     |   5000 |    4
     4 | s3      | sales     |   5000 |    4
     3 | s2      | sales     |   4800 |    6
     9 | d3      | develop   |   4500 |    7
     7 | d1      | develop   |   4200 |    8
     6 | p3      | personnel |   4100 |    9
     2 | p1      | personnel |   3900 |   10
     5 | p2      | personnel |   3500 |   11
(11 rows)

接着在各自部门内排名：

1
select empno, empname, deptname, salary, rank() over(partition by deptname order by salary desc) from employee_tbl;

得到结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | rank
-------+---------+-----------+--------+------
     8 | d2      | develop   |   6000 |    1
    11 | d5      | develop   |   5200 |    2
    10 | d4      | develop   |   5200 |    2
     9 | d3      | develop   |   4500 |    4
     7 | d1      | develop   |   4200 |    5
     6 | p3      | personnel |   4100 |    1
     2 | p1      | personnel |   3900 |    2
     5 | p2      | personnel |   3500 |    3
     1 | s1      | sales     |   5000 |    1
     4 | s3      | sales     |   5000 |    1
     3 | s2      | sales     |   4800 |    3
(11 rows)

观察上述两个案例中的rank()得到的排名，如果有并列第N名，则排名序号会不连续。比如上面示例，并列第2名后直接跳到了第4名，没有第3名。如果想要名次编号连续，可以换成dense_rank()：

1
select empno,empname,deptname,salary,dense_rank() over(order by salary desc) from employee_tbl;

得到结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | dense_rank
-------+---------+-----------+--------+------------
     8 | d2      | develop   |   6000 |          1
    11 | d5      | develop   |   5200 |          2
    10 | d4      | develop   |   5200 |          2
     1 | s1      | sales     |   5000 |          3
     4 | s3      | sales     |   5000 |          3
     3 | s2      | sales     |   4800 |          4
     9 | d3      | develop   |   4500 |          5
     7 | d1      | develop   |   4200 |          6
     6 | p3      | personnel |   4100 |          7
     2 | p1      | personnel |   3900 |          8
     5 | p2      | personnel |   3500 |          9
(11 rows)

可见，使用dense_rank()后，并列第2名之后就是并列第3名。

在员工工资记录后展示各部门工资总额列

直接使用sum()聚合函数对应的窗口函数：

1
select empno,empname,deptname,salary,(sum(salary) over(partition by deptname)) as dept_total_salary from employee_tbl order by empno asc;

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | dept_total_salary
-------+---------+-----------+--------+-------------------
     1 | s1      | sales     |   5000 |             14800
     2 | p1      | personnel |   3900 |             11500
     3 | s2      | sales     |   4800 |             14800
     4 | s3      | sales     |   5000 |             14800
     5 | p2      | personnel |   3500 |             11500
     6 | p3      | personnel |   4100 |             11500
     7 | d1      | develop   |   4200 |             25100
     8 | d2      | develop   |   6000 |             25100
     9 | d3      | develop   |   4500 |             25100
    10 | d4      | develop   |   5200 |             25100
    11 | d5      | develop   |   5200 |             25100
(11 rows)

在员工工资记录后展示各部门截止当前员工记录行累加得到的工资总额列

直接使用sum()聚合函数对应的窗口函数, 注意over子句要使用order by：

1
select empno,empname,deptname,salary,(sum(salary) over(partition by deptname order by empno asc)) as dept_total_salary from employee_tbl order by deptname asc, empno asc;

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | dept_total_salary
-------+---------+-----------+--------+-------------------
     7 | d1      | develop   |   4200 |              4200
     8 | d2      | develop   |   6000 |             10200
     9 | d3      | develop   |   4500 |             14700
    10 | d4      | develop   |   5200 |             19900
    11 | d5      | develop   |   5200 |             25100
     2 | p1      | personnel |   3900 |              3900
     5 | p2      | personnel |   3500 |              7400
     6 | p3      | personnel |   4100 |             11500
     1 | s1      | sales     |   5000 |              5000
     3 | s2      | sales     |   4800 |              9800
     4 | s3      | sales     |   5000 |             14800
(11 rows)

这里需要特别注意一下，dept_total_salary是首先按部门分组，再对部门内员工记录按empno升序排序，然后累加计算组内(本例中即部门内)第一条记录到当前记录的工资得到的。

获取工资前3高的员工

需要先用窗口函数对员工按工资排序，然后排序号<4的几条记录。

1
2
3
select * from (
  select empno,empname,deptname,salary,(rank() over(order by salary desc)) as pos from employee_tbl
) as a where a.pos < 4;

也可以根据需要取前3条记录:

1
2
3
select * from (
  select empno,empname,deptname,salary,(rank() over(order by salary desc)) as pos from employee_tbl
) as a limit 3;

或：

1
select empno,empname,deptname,salary,(rank() over(order by salary desc)) as pos from employee_tbl limit 3;

在员工工资记录后展示所在部门截止当前员工记录的工资总额和平均工资

这里需要两个窗口函数，可以复用partition by xxx order by yyy部分：

1
2
select empno,empname,deptname,(sum(salary) over w) as dept_total_salary, round(avg(salary) over w) as avg_salary FROM employee_tbl WINDOW w as
(partition by deptname order by salary asc);

结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | dept_total_salary | avg_salary
-------+---------+-----------+-------------------+------------
     7 | d1      | develop   |              4200 |       4200
     9 | d3      | develop   |              8700 |       4350
    10 | d4      | develop   |             19100 |       4775
    11 | d5      | develop   |             19100 |       4775
     8 | d2      | develop   |             25100 |       5020
     5 | p2      | personnel |              3500 |       3500
     2 | p1      | personnel |              7400 |       3700
     6 | p3      | personnel |             11500 |       3833
     3 | s2      | sales     |              4800 |       4800
     1 | s1      | sales     |             14800 |       4933
     4 | s3      | sales     |             14800 |       4933
(11 rows)

吐槽一下，感觉这个需求好没意义，纯粹为了炫技。
同样的，开头计算部门平均工资以及员工工资和部门平均工资偏差比例的sql也可以稍微简化一下：

1
2
3
4
SELECT empno,empname,deptname,salary,round(avg(salary) over w) as avg_salary, round(salary/(avg(salary) over w)-1, 2) as diff_ratio 
FROM employee_tbl
WINDOW w as (partition by deptname)
order by empno asc;

执行结果跟原始语句相同：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 empno | empname | deptname  | salary | avg_salary | diff_ratio
-------+---------+-----------+--------+------------+------------
     1 | s1      | sales     |   5000 |       4933 |       0.01
     2 | p1      | personnel |   3900 |       3833 |       0.02
     3 | s2      | sales     |   4800 |       4933 |      -0.03
     4 | s3      | sales     |   5000 |       4933 |       0.01
     5 | p2      | personnel |   3500 |       3833 |      -0.09
     6 | p3      | personnel |   4100 |       3833 |       0.07
     7 | d1      | develop   |   4200 |       5020 |      -0.16
     8 | d2      | develop   |   6000 |       5020 |       0.20
     9 | d3      | develop   |   4500 |       5020 |      -0.10
    10 | d4      | develop   |   5200 |       5020 |       0.04
    11 | d5      | develop   |   5200 |       5020 |       0.04
(11 rows)

发表于 更新于 本文字数： 604 阅读时长 ≈ 2 分钟

psql命令

列出所有数据库:

1
psql -l

进入指定数据库：

1
psql -d my_db_name

带用户名密码进入指定数据库:

1
psql -h your_host_name_or_ip -p your_port -U username -W

执行sql文件：

1
psql -d my_db_name -f a.sql

通过一条终端指令执行简单操作：

1
psql -d my_db_name -c "\dt"

上述指令可替代如下三条指令构成的操作序列：

1
2
3
psql -d my_db_name
\dt
\q

单步执行SQL指令，每一步都要敲回车确认:

1
psql -s

查看版本号：

1
psql -V

数据库信息查看

查看有哪些用户:

1
\du

切换数据库:

1
\c my_db_name

查看有哪些数据库：

1
\l

查看当前数据库下有哪些表:

1
\dt

查看某个表的信息:

1
\d my_table_name

查看有哪些索引:

1
\di

查看有哪些表空间：

1
\db

退出psql：

1
\q

执行外部SQL文件:

1
\i a.sql

把后续执行结果写入外部文件,直到退出psql:

1
\o output_file.txt

查看可用指令列表：

1
\?

查看某个命令的语法帮助:

1
\h create table

数据库使用状态查看

查看数据库占用空间大小:

1
select pg_database_size('my_test_db');

查看所有数据库占用空间的大小:

1
select datname, pg_database_size(datname) AS size from pg_database;

查看所有数据库占用空间大小，并以KB、MB等可读方式显示:

1
select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;

查看表或索引大小:

1
select pg_relation_size('my_table_or_index_name');

查看表的总大小，包括索引:

1
select pg_size_pretty(pg_total_relation_size(my_table));

查看表空间大小:

1
select pg_size_pretty(pg_tablespace_size('pg_default'));

数据库、用户设置

创建用户:

1
create user my_test_user with password 'my_password';

创建数据库:

1
create database my_test_db;

把数据库分配给用户:

1
alter database my_test_db owner my_test_user;

直接创建用户并设置属主：

1
create database my_test_db_2 owner my_test_user;

别忘了把权限赋予用户:

1
grant all privileges on database my_test_db to my_test_user;

如果用新创建的数据库用户登录报以下错误：
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "my_test_user"
这是因为，该机器上psql的连接建立于Unix Socket上默认使用peer authentication，必须要用和数据库用户相同的系统用户进行登录。
解决办法也简单，要么创建对应的操作系统用户my_test_user后sudo su - my_test_user再用psql -d my_test_db登录，要么把登录认证方式从peer authentiction改为md5。

如果要收回权限，使用下面语句:

1
revoke all on database my_test_db from my_test_user;

删除数据库用户，使用下面语句：

1
drop user my_test_user;

发表于 本文字数： 310 阅读时长 ≈ 1 分钟

换电脑后需要给github配置新的ssh key以便于通过ssh协议免密push代码。使用ssh-keygen -t rsa -C "xx@xx.com" 在本机生成ssh密钥对后，需要把~/.id_rsa.pub公钥文件的内容复制出来，再粘贴到github账号里。通常做法是用诸如记事本、notpad++等文本编辑软件直接打开id_rsa.pub文件，然后Ctrl+A、Ctrl+C复制到系统剪贴板即可。不过为了显得有技术范(装逼)，想试试直接用命令行执行这一动作。一时记不起来windows下如何用命令行复制文件内容了，查了些资料，整理如下。

Windows PowerShell

对于PowerShell, 直接使用如下命令：

1
cat id_rsa.pub | clip

Windows CommandLine

对于传统的cmd命令行，直接使用如下命令：

1
clip < id_rsa.pub

Linux Shell

对于Linux系统来说，反倒比较麻烦一点点，需要先安装外部小工具，xsel、xclip, 使用yum或apt-get安装都可以。
如果是xclip，直接用

1
cat id_rsa.pub | xclip

如果是xsel, 使用

1
cat id_rsa.pub | xsel

内容将复制到系统剪贴板。注意，一般云厂商提供的服务器没有配置输入输出设备，这俩命令都无效。

发表于 更新于 本文字数： 341 阅读时长 ≈ 1 分钟

如下DataFrame, 一列日期，一列时间，现需要把日期和时间合并为一列。

1
df = pd.DataFrame([['2023-01-05','0930',100],['2023-01-05','0935',200],['2023-01-05','0940',333]],columns=['日期','时间','数量'])
1
2
3
4
       日期    时间   数量
0  2023-01-05  0930  100
1  2023-01-05  0935  200
2  2023-01-05  0940  333

最简单的做法是直接两列按字符串拼接：

1
df['日期时间1'] = df['日期'] + ' ' + df['时间']

如果要合并的列不是字符串，则需显式转化一下再合并：

1
df['日期时间2'] = df['日期'].astype(str) + ' ' + df['时间'].astype(str)

还可以这样：

1
df['日期时间3'] = df[['日期','时间']].apply(lambda x: ' '.join(x), axis=1)

或者：

1
df['日期时间4'] = df.apply(lambda x: x['日期'] + ' ' + x['时间'], axis=1)

如果要合并的列中有nan,则可以使用这个：

1
df['日期时间5'] = df['日期'].str.cat(df['时间'], sep=' ', na_rep='?')

最后效果如下:

1
2
3
4
5
>>> df
       日期    时间   数量      日期时间1        日期时间2        日期时间3         日期时间4        日期时间5
0  2023-01-05  0930  100  2023-01-05 0930  2023-01-05 0930  2023-01-05 0930  2023-01-05 0930  2023-01-05 0930
1  2023-01-05  0935  200  2023-01-05 0935  2023-01-05 0935  2023-01-05 0935  2023-01-05 0935  2023-01-05 0935
2  2023-01-05  0940  333  2023-01-05 0940  2023-01-05 0940  2023-01-05 0940  2023-01-05 0940  2023-01-05 0940

完毕！

发表于 本文字数： 247 阅读时长 ≈ 1 分钟

如下DataFrame, 有三个列site、material、LT，现需要根据material把site去重后合并，并取出LT最大值。

1
2
3
4
5
6
7
df = pd.DataFrame([['FJZ','A123',123],
       ['FOC','A123',456],
       ['FJZ','B456',112],
       ['FJZ','B456',245],
       ['FJZ','B456',110],
       ['FOC','C789',202],
       ['FOC','C789',205]],columns=['site','material','LT'])

原始DataFrame数据如下：

1
2
3
4
5
6
7
8
  site material   LT
0  FJZ     A123  123
1  FOC     A123  456
2  FJZ     B456  112
3  FJZ     B456  245
4  FJZ     B456  110
5  FOC     C789  202
6  FOC     C789  205

直接groupby后join:

1
df.groupby('material').agg({'site': ','.join, 'LT': max}).reset_index()

处理后数据如下：

1
2
3
4
  material         site   LT
0     A123      FJZ,FOC  456
1     B456  FJZ,FJZ,FJZ  245
2     C789      FOC,FOC  205

第二行数据中，site列的FJZ出现了重复。
这里我们换一种思路，由于site列涉及去重、拼接两个步骤，可以先定义一个处理函数，把这两个步骤串起来。

1
2
def agg_func(items):
    return ','.join(set(items))

然后再调用这个函数：

1
df.groupby('material').agg({'site': agg_func, 'LT': max}).reset_index()

最后效果如下:

1
2
3
4
  material     site   LT
0     A123  FJZ,FOC  456
1     B456      FJZ  245
2     C789      FOC  205

完毕！

0%