src/FluxInterpolationUtil_4.scala
1package cn.piflow.bundle.flux.util
2
3
4import org.apache.spark.sql.{DataFrame, SparkSession}
5
6/**
7 * 通量数据插补
8 */
9
10class FluxInterpolationUtil_4 {
11
12
13 /**
14 * 插补算法: MDV-I:固定窗口
15 * 平均日变化法(MDV)/半经验方法
16 * @param spark
17 * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
18 * @param fields 需要插补的字段,多个以逗号分隔
19 * @param mdv_num 窗口时间
20 * @param flagValue 质量控制标识
21 * @return
22 */
23 def MDVFixed(spark:SparkSession,originDF:DataFrame,fields:String,mdv_num:Int,flagValue:Int):DataFrame={
24 spark.catalog.dropTempView("MDVFixed")
25 originDF.createOrReplaceTempView("MDVFixed")
26// 根据窗口 对通量数据进行分区
27 val dayZoneDF = spark.sql(s"select * ,ceiling(day_id/${mdv_num}) as zone from MDVFixed")
28 dayZoneDF.createOrReplaceTempView("dayZoneDF")
29
30 val zoneAvgSqlBuilder = new StringBuilder()
31 val supplementSqlBuilder = new StringBuilder()
32
33 var df = originDF
34 fields.split(",").foreach(field => {
35 // 统计每个分区的平均数
36 val zoneAvgStr = s"round(avg(if(${field} > ${flagValue},${field},0)),5) as `${field}`"
37 zoneAvgSqlBuilder.append(zoneAvgStr+",")
38 // 插补无效值为分区平均值
39 val supplementStr= s"if( a.${field} > ${flagValue},a.${field},b.`${field}`) as `${field}`"
40 supplementSqlBuilder.append(supplementStr+",")
41
42 df = df.drop(field)
43 })
44
45// 插补计算
46 val interpolationCalculationDF = spark.sql(
47 s"""
48 | select day_id,a.hour ,${supplementSqlBuilder} a.zone from dayZoneDF a
49 | left join (
50 | select ${zoneAvgSqlBuilder} zone,hour from dayZoneDF group by zone,hour
51 | )b
52 | on a.zone =b.zone and a.hour= b.hour
53 |""".stripMargin)
54
55 val resultDF=df.join(interpolationCalculationDF,Seq("day_id","hour"))
56
57 resultDF
58 }
59
60
61 /**
62 * 插补算法: MDV-G:滑动窗口
63 * 平均日变化法(MDV)/半经验方法
64 * @param spark
65 * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
66 * @param field
67 * @param mdv_num 窗口时间 ,必须为奇数
68 * @param flagValue 质量控制标识
69 * @return
70 */
71 def MDVSliding(spark:SparkSession,originDF:DataFrame,fields:String,mdv_num:Int,flagValue:Int):DataFrame={
72 // 根据窗口时间计算前后间隔
73 val day_range= math.floor(mdv_num / 2.0).toInt
74 println("滑动窗口前后间隔时间--->"+day_range)
75
76 spark.catalog.dropTempView("mdvSlidingDF")
77 originDF.createOrReplaceTempView("mdvSlidingDF")
78
79 val builder: StringBuilder = new StringBuilder
80 val avgBuilder: StringBuilder = new StringBuilder
81 fields.split(",").foreach(x=>{
82 builder.append(s"b.${x} as ${x} ,")
83 avgBuilder.append(s"round( if( sum(if(${x}= ${flagValue},0,1)) = 0, ${flagValue}, sum(if(${x}= ${flagValue},0,${x})) / sum(if(${x}= ${flagValue} ,0,1)) ),5) as ${x} ,")
84
85 })
86
87 val avgWindowsDF = spark.sql(
88 s"""
89 | select day_id,hour, ${avgBuilder.toString.stripSuffix(",")} from (
90 | select a.day_id,a.hour,${builder.toString.stripSuffix(",")} from mdvSlidingDF a
91 | left join mdvSlidingDF b
92 | on a.day_id >= b.day_id-${day_range} and a.day_id <= b.day_id+${day_range} and a.day_id<>b.day_id and a.hour=b.hour
93 |) a group by day_id,hour
94 |""".stripMargin)
95
96 avgWindowsDF.createOrReplaceTempView("avgWindowsDF")
97 val resultBuilder: StringBuilder = new StringBuilder
98 originDF.schema.fieldNames.foreach(x=>{
99 if(fields.split(",").contains(x)) resultBuilder.append(s" if(b.${x} is not null and a.${x} = ${flagValue},b.${x},a.${x}) as ${x} ,")
100 else resultBuilder.append(s"a.${x} ,")
101 })
102
103 val MDVSlidingDF = spark.sql(
104 s"""
105 |
106 | select ${resultBuilder.toString.stripSuffix(",")} from mdvSlidingDF a
107 | left join avgWindowsDF b
108 | on a.day_id = b.day_id and a.hour = b.hour
109 |
110 |""".stripMargin)
111
112 MDVSlidingDF
113 }
114
115 def MDVSliding_old(spark:SparkSession,originDF:DataFrame,field:String,mdv_num:Int,flagValue:Int):DataFrame={
116 // 根据窗口时间计算前后间隔
117 val day_range= math.ceil(mdv_num / 2.0).toInt
118
119 spark.catalog.dropTempView("originDF")
120 originDF.repartition(30).createOrReplaceTempView("originDF")
121 println(originDF.rdd.getNumPartitions)
122
123 // 插补计算
124 val resultDF = spark.sql(
125 s"""
126 |select a.day_id,a.hour,if(b.avg_num is not null,b.avg_num,a.${field}) as `${field}` from originDF a
127 |left join (
128 | select day_id,hour, avg(${field}) as avg_num from (
129 | select a.day_id,a.hour, b.${field} from (select * from originDF where ${field} = ${flagValue})a
130 | left join originDF b
131 | on a.day_id >= b.day_id-${day_range} and a.day_id <= b.day_id+${day_range} and a.day_id<>b.day_id and a.hour=b.hour
132 | )a where ${field} <> ${flagValue} group by day_id,hour
133 |)b on a.day_id=b.day_id and a.hour=b.hour
134 |""".stripMargin)
135
136 // val resultDF= originDF.drop(field).join(interpolationCalculationDF,Seq("day_id","hour"))
137
138 resultDF
139 }
140
141
142
143 /**
144 * 间隔插补1:----------------------------------------
145 * 当前值无效M(k),前后为有效值M(k-1)、M(k+1),当前值 为 M(k)=(M(k-1)+M(k+1))/2.0
146 * @param spark
147 * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
148 * @param field 需要进行插补的字段,多個字段以逗號分隔
149 * @param flagValue 质量控制标记值:Quality control marker value
150 * @return
151 */
152 def ForwardAndBackwardInterpolation(spark:SparkSession,originDF:DataFrame,fields: String,flagValue:Int):DataFrame ={
153
154 val schemaFieldNames = originDF.schema.fieldNames
155
156 val avgString = new StringBuilder
157 val compareString = new StringBuilder
158
159 schemaFieldNames.foreach(x=>{
160 if(fields.split(",").contains(x)){
161 avgString.append(s"if(a.${x}= ${flagValue} or b.${x}= ${flagValue} ,${flagValue},round((a.${x}+b.${x})/2,5) ) as ${x} ,")
162 compareString.append(s"if(a.${x} = ${flagValue} and b.${x}<> ${flagValue},b.${x},a.${x}) as ${x} ,")
163 } else {
164 avgString.append(s"a.${x} ,")
165 compareString.append(s"a.${x} ,")
166 }
167 })
168
169 originDF.createOrReplaceTempView("originDF")
170 val frontAndBackAverage = spark.sql(
171 s"""
172 |select a.day_id+1 as day_id_b,${avgString.toString.stripSuffix(",")} from originDF a
173 |left join originDF b
174 |on a.day_id=b.day_id-2 and a.hour = b.hour
175 |""".stripMargin)
176 frontAndBackAverage.createOrReplaceTempView("frontAndBackAverage")
177
178 val resultDF = spark.sql(
179 s"""
180 |select ${compareString.toString.stripSuffix(",")} from originDF a
181 |left join frontAndBackAverage b
182 | on a.day_id=b.day_id_b and a.hour = b.hour
183 |
184 |""".stripMargin)
185 resultDF
186 }
187
188
189
190 /**
191 * 间隔插补1:
192 * 当前值无效M(k),前后为有效值M(k-1)、M(k+1),当前值 为 M(k)=(M(k-1)+M(k+1))/2.0
193 * @param spark
194 * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
195 * @param field 需要进行插补的字段
196 * @param flagValue 质量控制标记值:Quality control marker value
197 * @return
198 */
199 def ForwardAndBackwardInterpolation_bak(spark:SparkSession,originDF:DataFrame,field: String,flagValue:Int):DataFrame ={
200
201 originDF.select("day_id","hour",field).createOrReplaceTempView("originDF")
202 // 查询出当前值无效的数据 及其前后的数据
203 val CurrentValueIsInvalidDF = spark.sql(
204 s"""
205 |select a.day_id ,a.hour,b.${field} from (select * from originDF where ${field}=${flagValue})a
206 | left join originDF b on (a.day_id = b.day_id-1 or a.day_id = b.day_id+1 ) and a.hour=b.hour and a.day_id<>b.day_id and b.${field}<>${flagValue}
207 |""".stripMargin)
208 CurrentValueIsInvalidDF.createOrReplaceTempView("CurrentValueIsInvalidDF")
209
210// CurrentValueIsInvalidDF.orderBy("day_id","hour").show(100)
211
212
213 // 过滤前后为有效值的数据,然后求平均数 ,替换原始无效值
214 val MeetTheConditionsDF = spark.sql(
215 s"""
216 |select a.day_id,a.hour,if(b.avg_num is not null,b.avg_num,a.${field}) as `${field}` from originDF a
217 |left join (
218 | select day_id,hour,avg(${field}) as avg_num from (
219 | select * from CurrentValueIsInvalidDF where concat(day_id,hour) in (select concat(day_id,hour) from CurrentValueIsInvalidDF
220 | group by day_id ,hour having count(*)>1 )
221 | ) group by day_id,hour
222 |)b on a.day_id=b.day_id and a.hour=b.hour
223 |""".stripMargin)
224
225 val resultDF = originDF.drop(field).join(MeetTheConditionsDF,Seq("day_id","hour"))
226
227 resultDF
228 }
229
230
231 /**
232 * 间隔插补2:
233 * 当前值无效M(k),M(k+1)无效,M(k-1)有效、M(k+2)有效
234 * M(k)=M(k-1)+ abs(M(k-1)-M(k+2))/3
235 * M(k+1)=M(k-1)+ abs(M(k-1)-M(k+2))*2/3
236 * @param spark
237 * @param originDF schema 必须包含 hour, day_id(按照日期递增的 id)
238 * @param field 需要进行插补的字段
239 * @param flagValue 质量控制标记值:Quality control marker value
240 * @return
241 */
242 def CurrentAndPostValueInterpolation(spark:SparkSession,originDF:DataFrame,fields: String,flagValue:Int):DataFrame ={
243
244 val interpolation = new InterpolationMetro
245 val interpolationResultDF = interpolation.imputationReplacement(spark,originDF,fields,24*3600,flagValue)
246
247 interpolationResultDF
248 }
249
250
251
252
253}