postgreSQL学习笔记（三）

聚合函数

对一组值进行计算，返回单个值，将多行数据压缩成一个统计结果

常用函数

COUNT(*):统计行数
SUM(colum)
AVG(column)：计算平均值
MAX(column)
MIN(column)

-- 计算总员工数
SELECT COUNT(*) FROM employees;

-- 计算公司月薪总支出
SELECT SUM(salary) FROM employees;

GROUP BY子句

用于将数据按一列或多列进行分组

在SELECT语句中出现的列，并且没被聚合函数包裹都必须出现在GROUP BY子句中

SELECT
    grouping_column,
    aggregate_function(column_to_aggregate)
FROM
    table_name
GROUP BY
    grouping_column;

窗口函数

不改变原始行数，在每一行后面添加聚合计算结果

GROUP BY是进行分组压缩，输出组的结果
窗口函数是新添加一列计算结果

OVER子句

所有的窗口函数都是用OVER子句定义计算的窗口
OVER():窗口包含表中的所有行
OVER(PARTITION BY):将窗口内部按列分区
OVER(ORDER BY):定义窗口内的排序规则

常用的窗口函数

聚合类: AVG(), SUM(), COUNT() 等聚合函数都可以用作窗口函数。

排名类:
RANK(): 标准排名，并列时会跳过后续名次 (e.g., 1, 2, 2, 4)。

DENSE_RANK(): 紧凑排名，并列时不会跳过后续名次 (e.g., 1, 2, 2, 3)。
计算每个员工在其部门内的薪水排名。

SELECT
    name,
    department,
    salary,
    -- 先按部门分区，再在部门内按薪水降序排列，然后计算排名
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_salary_rank
FROM
    employees;

DAO模式

数据通常存储在数据库里（MySQL、PostgreSQL、Oracle…），但业务逻辑代码（比如计算订单总价、校验用户权限）并不应该直接和数据库的 SQL 语句耦合。
如果业务逻辑层直接写一堆 Connection、Statement、ResultSet，会有几个问题：

耦合度高：业务逻辑和数据库代码混在一起，难以维护。
可移植性差：换数据库（MySQL → Oracle）就得改很多地方。
测试困难：想单独测试业务逻辑很难，因为总是依赖真实数据库。
java中通常分为以下几层

Controller (界面/接口层)
    ↓
Service (业务逻辑层)
    ↓
DAO (数据访问层)
    ↓
Database (数据库)

DAO 一般包含以下部分：

DAO 接口：定义数据访问的操作
DAO 实现类：实现具体的 SQL 查询、增删改查逻辑
实体类 (Entity/Model)：对应数据库表的一条记录

代码流程分为以下几步
1.定义实体类

public class User {
    private int id;
    private String name;
    private String email;

    // getter & setter
}

2.定义DAO接口

public interface UserDao {
    User findById(int id);
    List<User> findAll();
    void save(User user);
    void update(User user);
    void delete(int id);
}

3.实现接口

public class UserDaoImpl implements UserDao {
    private Connection connection;
    public UserDaoImpl(Connection connection) {
        this.connection = connection;
    }
    @Override
    public User findById(int id) {
        try {
            PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE id=?");
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setEmail(rs.getString("email"));
                return u;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

4.在Service层中调用

public class UserService {
    private UserDao userDao;
    public UserService(UserDao userDao) {
        this.userDao = userDao;
    }
    public void registerUser(User user) {
        userDao.save(user);
    }
}