碧水源数据分析工程师岗位知识题及答案.docxVIP

碧水源数据分析工程师岗位知识题及答案

一、基础技能题

1.请用SQL编写查询语句，计算某污水厂2023年1-6月各月份进水COD浓度的平均值、最大值、最小值，以及该月份内COD超标（排放标准为500mg/L）的天数占比（要求保留2位小数）。数据存于表`water_quality`，字段包括：`station_id`（站点ID）、`monitor_date`（监测日期）、`cod`（COD浓度）。

答案：

```sql

SELECT

DATE_FORMAT(monitor_date,%Y-%m)ASmonth,

ROUND(AVG(cod),2)ASavg_cod,

MAX(cod)ASmax_cod,

MIN(cod)ASmin_cod,

ROUND(SUM(CASEWHENcod500THEN1ELSE0END)100.0/COUNT(),2)ASover_standard_rate

FROM

water_quality

WHERE

station_id=目标污水厂ID--假设限定具体站点

ANDmonitor_dateBETWEEN2023-01-01AND2023-06-30

GROUPBY

DATE_FORMAT(monitor_date,%Y-%m)

ORDERBY

month;

```

关键思路：

-使用`DATE_FORMAT`提取月份，按月份分组聚合；

-通过`CASEWHEN`统计超标天数（cod500时计为1），结合`COUNT()`计算总天数，得到超标占比；

-`ROUND`函数控制小数位数，确保结果可读性。

2.某水质监测站的pH值数据（单位：无量纲）存在以下问题：部分日期缺失（缺失率约15%），且存在异常值（如pH=14.5或pH=3.2，而正常范围为6-9）。请设计Python代码完成以下处理：

（1）识别并处理缺失值；

（2）识别并处理异常值；

（3）输出处理后数据的统计摘要（均值、标准差、分位数）。

答案：

```python

importpandasaspd

importnumpyasnp

fromscipyimportstats

假设数据已读取为df，包含monitor_date（日期）和ph（pH值）字段

df[monitor_date]=pd.to_datetime(df[monitor_date])

df.set_index(monitor_date,inplace=True)

（1）缺失值处理：时间序列插值（线性插值或前向填充）

检查缺失情况

missing_rate=df[ph].isnull().mean()100

print(f原始缺失率：{missing_rate:.2f}%)

线性插值（适用于时间序列连续性强的场景）

df[ph]=df[ph].interpolate(method=time)按时间间隔插值

若仍有缺失（如开头/结尾），用前向/后向填充

df[ph].fillna(method=ffill,inplace=True)

df[ph].fillna(method=bfill,inplace=True)

（2）异常值处理：基于IQR方法（pH正常范围已知时优先业务规则，未知时用统计方法）

方法1：业务规则过滤（已知正常范围6-9）

df=df[(df[ph]=6)(df[ph]=9)]

方法2（备用）：统计方法（如Z-score或IQR）

q1=df[ph].quantile(0.25)

q3=df[ph].quantile(0.75)

iqr=q3-q1

lower_bound=q1-1.5iqr

upper_bound=q3+1.5iqr

替换异常值为边界值（或删除，根据业务需求）

df[ph]=np.where(df[ph]lower_bound,lower_bound,df[ph])

df[ph]=np.where(df[ph]upper_bound,upper_bound,df[ph])

（3）输出统计摘要

stats_summary=df[ph].describe(percentiles=[0.25,0.5,0.75,0.95])

stats_summary[std]=df[ph].std()describe默认含std，此处仅示例

print(处理后