pyodbc库删除MSSQL表中重复字段及排序函数ROW_NUMBER()运用

谁践踏了优雅 2024-02-20 12:47 3阅读 0赞

`ROW_NUMBER()` 是一个窗口函数，用于在 SQL 查询结果集中为每一行分配一个唯一的数字标识符。这个标识符通常用于数据排序、分组或者在结果集中标识特定行。

`ROW_NUMBER()` 函数的语法如下：

ROW_NUMBER() OVER (
        [PARTITION BY column1, column2, ...]
        [ORDER BY column3, column4, ...]
    )

*  `PARTITION BY` 子句是可选的，用于将结果集按照指定的列进行分区。在每个分区中，`ROW_NUMBER()` 将从 1 开始为每行分配唯一标识符。
 *  `ORDER BY` 子句也是可选的，用于指定按照哪些列对行进行排序。如果指定了 `ORDER BY`，`ROW_NUMBER()` 将根据排序列的顺序为每行分配唯一标识符。

举个例子，假设我们有一个名为 `employees` 的表，包含以下列：`id`、`name`、`department` 和 `salary`。我们想要查询每个部门的员工按照薪资降序排列的名字和薪资，同时为每个部门的员工按照薪资排名： 在这个例子中，我们使用了 `PARTITION BY` 子句按照 `department` 对结果集进行了分区，然后使用 `ORDER BY` 子句按照 `salary` 对每个部门的员工进行排序。`ROW_NUMBER()` 函数为每个部门的员工分配了唯一的排名。

SELECT
        name,
        salary,
        department,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
    FROM
        employees
    ORDER BY
        department,
        rank;

**pyodbc库删除MSSQL中重复字段及排序函数ROW\_NUMBER()运用**

import pyodbc
    import warnings
    
    import pandas as pd  # 导入pandas库
    
    warnings.filterwarnings('ignore') #隐藏Pandas警告SQLAlchemy
    # 读取Excel文件
    file_path = 'C:\\Users\\Administrator\\Documents\\Employees.xlsx'
    df = pd.read_excel(file_path,sheet_name="Sheet1")
    
    print(df.info())
    
    # 连接到MSSQL数据库
    server = '127.0.0.1'
    database = 'tsl'
    username = 'sa'
    password = 'lq8xxcc'
    
    cnxn_string = f'DRIVER={
       {ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'
    cnxn = pyodbc.connect(cnxn_string)
    cursor = cnxn.cursor()
    
    # # # 将数据行插入到MSSQL数据库中
    # for index, row in df.iterrows():
    #     column1 = row[0]
    #     column2 = row[1]
    #     column3 = row[2]
    #     insert_query = f"INSERT INTO Employees (CustomerId,CustomerName,Email) VALUES (?, ?, ?)"
    #     cursor.execute(insert_query, column1,column2,column3)
    
    
    # ROW_NUMBER() 函数需要一个 ORDER BY 子句。
    # 有时候，为了满足语法的要求，但又不需要对结果进行实际排序，可以使用 ORDER BY (SELECT 0)。
    
    # OVER 是 SQL 中用于配合窗口函数使用的子句。它指定了窗口函数计算所涉及的数据子集，这个子集称为“窗口”（window）。
    # 窗口函数执行一些计算，例如 ROW_NUMBER()、SUM()、AVG() 等，但是它们会基于整个查询结果集中定义的窗口进行操作。
    
    # WITH CTE AS (
    #     SELECT *,
    #            ROW_NUMBER() OVER(PARTITION BY Column1, Column2, ... ORDER BY (SELECT 0)) AS RowNumber
    #     FROM YourTable
    # )
    # DELETE FROM CTE WHERE RowNumber > 1;
    
    # 首先使用一个公共表表达式（CTE）来对表中的ID进行分区并为每个分区中的每一行分配一个行号。
    # 然后，它删除行号大于1的行，这意味着它只保留每个分区中的第一行，从而删除了重复的ID。
    sql_del_repeat = '''
    WITH CTE AS (
        SELECT CustomerId, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY (SELECT 0)) AS RowNum
        FROM Employees
    )
    DELETE FROM CTE WHERE RowNum > 1;
    '''
    cursor.execute(sql_del_repeat)
    # 提交更改
    cnxn.commit()
    
    cursor.execute('select * from Employees')
    rows=cursor.fetchall()
    for row in rows:
       print("%-7s %-11s %-3s"%(row[0],row[1],row[2]))
    
    # 关闭连接
    cursor.close()
    cnxn.close()

\--

**在Python中，你可以使用三引号（`'''`或`"""`）来编写多行字符串**。这样，你就可以在字符串中包含换行符，从而编写多行SQL语句。例如：

sql_query = '''
    SELECT *
    FROM table_name
    WHERE condition
    ORDER BY some_column;
    '''
    cursor.execute(sql_query)

\-  
**`pandas`库**读取名为`data.csv`的CSV文件，并将其存储为一个名为`df`的数据框。然后，使用**`drop_duplicates`方法删除重复的行**。最后，将不包含重复行的数据框保存为名为`data_no_duplicates.csv`的CSV文件。

import pandas as pd
    
    # 读取数据
    df = pd.read_csv('data.csv')
    
    # 删除重复行
    df.drop_duplicates(inplace=True)
    
    # 保存数据
    df.to_csv('data_no_duplicates.csv', index=False)