scala如下
val CustPhsU = spark.read.jdbc(PG_URL_LCZX, """(select obj_id from analy.vs_cust_org_voltrate limit 1) a""".stripMargin, PG_CONN_LCZX)
.withColumn("TRANSFORMER_ID",lit("A123"))
.withColumn("U1",lit("242.5"))
.withColumn("U2",lit("240.2"))
.withColumn("U3",lit("242.8"))
.withColumn("U4",lit("241.6"))
.withColumn("U5",lit("241"))
.withColumn("U6",lit("240.8"))
.withColumn("U7",lit("243.3"))
.withColumn("U8",lit("243.5"))
.withColumn("U9",lit("241.1"))
.withColumn("U10",lit("238"))
.withColumn("U11",lit("241.2"))
.withColumn("U12",lit("240.2"))
.withColumn("U13",lit("237.7"))
.withColumn("U14",lit("238"))
.withColumn("U15",lit("240.9"))
.withColumn("U16",lit("239.8"))
.withColumn("U17",lit("239"))
.withColumn("U18",lit("238.4"))
.withColumn("U19",lit("236.4"))
.withColumn("U20",lit("236"))
.withColumn("U21",lit("237.8"))
.withColumn("U22",lit("237"))
.withColumn("U23",lit("240.3"))
.withColumn("U24",lit("237.1"))
.withColumn("U25",lit("236.3"))
.withColumn("U26",lit("237.5"))
.withColumn("U27",lit("237.3"))
.withColumn("U28",lit("242.3"))
.withColumn("U29",lit("237.5"))
.withColumn("U30",lit("236.1"))
.withColumn("U31",lit("235.2"))
.withColumn("U32",lit("232.1"))
.withColumn("U33",lit("232"))
.withColumn("U34",lit("238.1"))
.withColumn("U35",lit("231.3"))
.withColumn("U36",lit("227.5"))
.withColumn("U37",lit("230.7"))
.withColumn("U38",lit("233.5"))
.withColumn("U39",lit("234.4"))
.withColumn("U40",lit("235.7"))
.withColumn("U41",lit("235.3"))
.withColumn("U42",lit("232.5"))
.withColumn("U43",lit("234"))
.withColumn("U44",lit("234.4"))
.withColumn("U45",lit("232.9"))
.withColumn("U46",lit("234.1"))
.withColumn("U47",lit("237.7"))
.withColumn("U48",lit("236.8"))
.withColumn("U49",lit("237.5"))
.withColumn("U50",lit("235.7"))
.withColumn("U51",lit("234"))
.withColumn("U52",lit("232.2"))
.withColumn("U53",lit("228.3"))
.withColumn("U54",lit("225.6"))
.withColumn("U55",lit("229.8"))
.withColumn("U56",lit("231.4"))
.withColumn("U57",lit("222.2"))
.withColumn("U58",lit("229.6"))
.withColumn("U59",lit("225.7"))
.withColumn("U60",lit("230.1"))
.withColumn("U61",lit("236.8"))
.withColumn("U62",lit("234"))
.withColumn("U63",lit("232"))
.withColumn("U64",lit("232.9"))
.withColumn("U65",lit("235.9"))
.withColumn("U66",lit("238.2"))
.withColumn("U67",lit("236.6"))
.withColumn("U68",lit("237"))
.withColumn("U69",lit("240.4"))
.withColumn("U70",lit("233.6"))
.withColumn("U71",lit("229.8"))
.withColumn("U72",lit("236.2"))
.withColumn("U73",lit("238.5"))
.withColumn("U74",lit("234.2"))
.withColumn("U75",lit("237.1"))
.withColumn("U76",lit("241.3"))
.withColumn("U77",lit("241.3"))
.withColumn("U78",lit("235.3"))
.withColumn("U79",lit("239.7"))
.withColumn("U80",lit("239.1"))
.withColumn("U81",lit("238.8"))
.withColumn("U82",lit("243.5"))
.withColumn("U83",lit("243.5"))
.withColumn("U84",lit("244"))
.withColumn("U85",lit("242.4"))
.withColumn("U86",lit("243.5"))
.withColumn("U87",lit("241.5"))
.withColumn("U88",lit("244.9"))
.withColumn("U89",lit("243.6"))
.withColumn("U90",lit("238.3"))
.withColumn("U91",lit("236.7"))
.withColumn("U92",lit("234.8"))
.withColumn("U93",lit("237.6"))
.withColumn("U94",lit("236.1"))
.withColumn("U95",lit("240.6"))
.withColumn("U96",lit("239.8"))
.withColumn("CUSTARRAY",array($"U1".cast(types.DoubleType), $"U2".cast(types.DoubleType), $"U3".cast(types.DoubleType), $"U4".cast(types.DoubleType), $"U5".cast(types.DoubleType), $"U6".cast(types.DoubleType), $"U7".cast(types.DoubleType), $"U8".cast(types.DoubleType), $"U9".cast(types.DoubleType),
$"U10".cast(types.DoubleType), $"U11".cast(types.DoubleType), $"U12".cast(types.DoubleType), $"U13".cast(types.DoubleType), $"U14".cast(types.DoubleType), $"U15".cast(types.DoubleType), $"U16".cast(types.DoubleType), $"U17".cast(types.DoubleType), $"U18".cast(types.DoubleType), $"U19".cast(types.DoubleType), $"U20".cast(types.DoubleType), $"U21".cast(types.DoubleType), $"U22".cast(types.DoubleType), $"U23".cast(types.DoubleType), $"U24".cast(types.DoubleType), $"U25".cast(types.DoubleType), $"U26".cast(types.DoubleType), $"U27".cast(types.DoubleType), $"U28".cast(types.DoubleType), $"U29".cast(types.DoubleType),
$"U30".cast(types.DoubleType), $"U31".cast(types.DoubleType), $"U32".cast(types.DoubleType), $"U33".cast(types.DoubleType), $"U34".cast(types.DoubleType), $"U35".cast(types.DoubleType), $"U36".cast(types.DoubleType), $"U37".cast(types.DoubleType), $"U38".cast(types.DoubleType), $"U39".cast(types.DoubleType), $"U40".cast(types.DoubleType), $"U41".cast(types.DoubleType), $"U42".cast(types.DoubleType), $"U43".cast(types.DoubleType), $"U44".cast(types.DoubleType), $"U45".cast(types.DoubleType), $"U46".cast(types.DoubleType), $"U47".cast(types.DoubleType), $"U48".cast(types.DoubleType), $"U49".cast(types.DoubleType),
$"U50".cast(types.DoubleType), $"U51".cast(types.DoubleType), $"U52".cast(types.DoubleType), $"U53".cast(types.DoubleType), $"U54".cast(types.DoubleType), $"U55".cast(types.DoubleType), $"U56".cast(types.DoubleType), $"U57".cast(types.DoubleType), $"U58".cast(types.DoubleType), $"U59".cast(types.DoubleType), $"U60".cast(types.DoubleType), $"U61".cast(types.DoubleType), $"U62".cast(types.DoubleType), $"U63".cast(types.DoubleType), $"U64".cast(types.DoubleType), $"U65".cast(types.DoubleType), $"U66".cast(types.DoubleType), $"U67".cast(types.DoubleType), $"U68".cast(types.DoubleType), $"U69".cast(types.DoubleType),
$"U70".cast(types.DoubleType), $"U71".cast(types.DoubleType), $"U72".cast(types.DoubleType), $"U73".cast(types.DoubleType), $"U74".cast(types.DoubleType), $"U75".cast(types.DoubleType), $"U76".cast(types.DoubleType), $"U77".cast(types.DoubleType), $"U78".cast(types.DoubleType), $"U79".cast(types.DoubleType), $"U80".cast(types.DoubleType), $"U81".cast(types.DoubleType), $"U82".cast(types.DoubleType), $"U83".cast(types.DoubleType), $"U84".cast(types.DoubleType), $"U85".cast(types.DoubleType), $"U86".cast(types.DoubleType), $"U87".cast(types.DoubleType), $"U88".cast(types.DoubleType), $"U89".cast(types.DoubleType),
$"U90".cast(types.DoubleType), $"U91".cast(types.DoubleType), $"U92".cast(types.DoubleType), $"U93".cast(types.DoubleType), $"U94".cast(types.DoubleType), $"U95".cast(types.DoubleType), $"U96".cast(types.DoubleType)))
CustPhsU.show(false)
println("CustPhsU=====>")
val TransPhsU = spark.read.jdbc(PG_URL_LCZX, """(select obj_id from analy.vs_cust_org_voltrate limit 1) a""".stripMargin, PG_CONN_LCZX)
.withColumn("TRANSFORMER_ID",lit("A123"))
.withColumn("V1",lit("243.6"))
.withColumn("V2",lit("240.5"))
.withColumn("V3",lit("242.8"))
.withColumn("V4",lit("240.9"))
.withColumn("V5",lit("239.9"))
.withColumn("V6",lit("239.9"))
.withColumn("V7",lit("242"))
.withColumn("V8",lit("243"))
.withColumn("V9",lit("240.7"))
.withColumn("V10",lit("238.3"))
.withColumn("V11",lit("240.8"))
.withColumn("V12",lit("240.8"))
.withColumn("V13",lit("239.9"))
.withColumn("V14",lit("239.1"))
.withColumn("V15",lit("241.3"))
.withColumn("V16",lit("240.6"))
.withColumn("V17",lit("240.1"))
.withColumn("V18",lit("239.4"))
.withColumn("V19",lit("238"))
.withColumn("V20",lit("237.6"))
.withColumn("V21",lit("238.5"))
.withColumn("V22",lit("238.3"))
.withColumn("V23",lit("240.4"))
.withColumn("V24",lit("238"))
.withColumn("V25",lit("237.8"))
.withColumn("V26",lit("237.5"))
.withColumn("V27",lit("239.1"))
.withColumn("V28",lit("241.9"))
.withColumn("V29",lit("237.6"))
.withColumn("V30",lit("237.5"))
.withColumn("V31",lit("236.6"))
.withColumn("V32",lit("233.7"))
.withColumn("V33",lit("233.9"))
.withColumn("V34",lit("239.4"))
.withColumn("V35",lit("233.2"))
.withColumn("V36",lit("231"))
.withColumn("V37",lit("232.4"))
.withColumn("V38",lit("235"))
.withColumn("V39",lit("234.1"))
.withColumn("V40",lit("235.6"))
.withColumn("V41",lit("237.2"))
.withColumn("V42",lit("233.8"))
.withColumn("V43",lit("236.3"))
.withColumn("V44",lit("236"))
.withColumn("V45",lit("235.4"))
.withColumn("V46",lit("237.1"))
.withColumn("V47",lit("239.8"))
.withColumn("V48",lit("241.3"))
.withColumn("V49",lit("240.4"))
.withColumn("V50",lit("236.4"))
.withColumn("V51",lit("237.1"))
.withColumn("V52",lit("235.4"))
.withColumn("V53",lit("234"))
.withColumn("V54",lit("233.6"))
.withColumn("V55",lit("234.9"))
.withColumn("V56",lit("236"))
.withColumn("V57",lit("230.1"))
.withColumn("V58",lit("235.1"))
.withColumn("V59",lit("231.9"))
.withColumn("V60",lit("236.7"))
.withColumn("V61",lit("240.8"))
.withColumn("V62",lit("237.5"))
.withColumn("V63",lit("234.9"))
.withColumn("V64",lit("237.8"))
.withColumn("V65",lit("240.1"))
.withColumn("V66",lit("242"))
.withColumn("V67",lit("239.9"))
.withColumn("V68",lit("241"))
.withColumn("V69",lit("242.9"))
.withColumn("V70",lit("241.6"))
.withColumn("V71",lit("236.4"))
.withColumn("V72",lit("241.1"))
.withColumn("V73",lit("242"))
.withColumn("V74",lit("241.3"))
.withColumn("V75",lit("240.7"))
.withColumn("V76",lit("245"))
.withColumn("V77",lit("244.3"))
.withColumn("V78",lit("241.8"))
.withColumn("V79",lit("243.8"))
.withColumn("V80",lit("242.5"))
.withColumn("V81",lit("241.8"))
.withColumn("V82",lit("246.4"))
.withColumn("V83",lit("244.8"))
.withColumn("V84",lit("245.2"))
.withColumn("V85",lit("244.3"))
.withColumn("V86",lit("245"))
.withColumn("V87",lit("242.7"))
.withColumn("V88",lit("247"))
.withColumn("V89",lit("243.2"))
.withColumn("V90",lit("241.3"))
.withColumn("V91",lit("239.3"))
.withColumn("V92",lit("239.1"))
.withColumn("V93",lit("238.6"))
.withColumn("V94",lit("238.5"))
.withColumn("V95",lit("241.7"))
.withColumn("V96",lit("240"))
.withColumn("TRANSARRAY",array($"V1".cast(types.DoubleType), $"V2".cast(types.DoubleType), $"V3".cast(types.DoubleType), $"V4".cast(types.DoubleType), $"V5".cast(types.DoubleType), $"V6".cast(types.DoubleType), $"V7".cast(types.DoubleType), $"V8".cast(types.DoubleType), $"V9".cast(types.DoubleType),
$"V10".cast(types.DoubleType), $"V11".cast(types.DoubleType), $"V12".cast(types.DoubleType), $"V13".cast(types.DoubleType), $"V14".cast(types.DoubleType), $"V15".cast(types.DoubleType), $"V16".cast(types.DoubleType), $"V17".cast(types.DoubleType), $"V18".cast(types.DoubleType), $"V19".cast(types.DoubleType), $"V20".cast(types.DoubleType), $"V21".cast(types.DoubleType), $"V22".cast(types.DoubleType), $"V23".cast(types.DoubleType), $"V24".cast(types.DoubleType), $"V25".cast(types.DoubleType), $"V26".cast(types.DoubleType), $"V27".cast(types.DoubleType), $"V28".cast(types.DoubleType), $"V29".cast(types.DoubleType),
$"V30".cast(types.DoubleType), $"V31".cast(types.DoubleType), $"V32".cast(types.DoubleType), $"V33".cast(types.DoubleType), $"V34".cast(types.DoubleType), $"V35".cast(types.DoubleType), $"V36".cast(types.DoubleType), $"V37".cast(types.DoubleType), $"V38".cast(types.DoubleType), $"V39".cast(types.DoubleType), $"V40".cast(types.DoubleType), $"V41".cast(types.DoubleType), $"V42".cast(types.DoubleType), $"V43".cast(types.DoubleType), $"V44".cast(types.DoubleType), $"V45".cast(types.DoubleType), $"V46".cast(types.DoubleType), $"V47".cast(types.DoubleType), $"V48".cast(types.DoubleType), $"V49".cast(types.DoubleType),
$"V50".cast(types.DoubleType), $"V51".cast(types.DoubleType), $"V52".cast(types.DoubleType), $"V53".cast(types.DoubleType), $"V54".cast(types.DoubleType), $"V55".cast(types.DoubleType), $"V56".cast(types.DoubleType), $"V57".cast(types.DoubleType), $"V58".cast(types.DoubleType), $"V59".cast(types.DoubleType), $"V60".cast(types.DoubleType), $"V61".cast(types.DoubleType), $"V62".cast(types.DoubleType), $"V63".cast(types.DoubleType), $"V64".cast(types.DoubleType), $"V65".cast(types.DoubleType), $"V66".cast(types.DoubleType), $"V67".cast(types.DoubleType), $"V68".cast(types.DoubleType), $"V69".cast(types.DoubleType),
$"V70".cast(types.DoubleType), $"V71".cast(types.DoubleType), $"V72".cast(types.DoubleType), $"V73".cast(types.DoubleType), $"V74".cast(types.DoubleType), $"V75".cast(types.DoubleType), $"V76".cast(types.DoubleType), $"V77".cast(types.DoubleType), $"V78".cast(types.DoubleType), $"V79".cast(types.DoubleType), $"V80".cast(types.DoubleType), $"V81".cast(types.DoubleType), $"V82".cast(types.DoubleType), $"V83".cast(types.DoubleType), $"V84".cast(types.DoubleType), $"V85".cast(types.DoubleType), $"V86".cast(types.DoubleType), $"V87".cast(types.DoubleType), $"V88".cast(types.DoubleType), $"V89".cast(types.DoubleType),
$"V90".cast(types.DoubleType), $"V91".cast(types.DoubleType), $"V92".cast(types.DoubleType), $"V93".cast(types.DoubleType), $"V94".cast(types.DoubleType), $"V95".cast(types.DoubleType), $"V96".cast(types.DoubleType)))
TransPhsU.show(false)
println("TransPhsU=====>")
// 步骤2: 通过TRANSID连接DataFrame
val joinedDFA = CustPhsU.join(TransPhsU, Seq("TRANSFORMER_ID"))
joinedDFA.show(false)
LeoUtils.logPrint(("joinedDFA", joinedDFA))
println(new Timestamp(System.currentTimeMillis()))
// 步骤3: 定义皮尔逊相关系数UDF
val pearsonCorr = udf((x: Seq[Any], y: Seq[Any]) => {
// 步骤1: 过滤无效值并转换为数字对
val validPairs = x.zip(y).flatMap {
case (a, b) if a != null && b != null => // 检查null
val aStr = a.toString.trim // 转换为字符串并去除空格
val bStr = b.toString.trim
if (aStr.nonEmpty && bStr.nonEmpty) { // 检查空白
// 安全转换为Double:成功则返回Some,失败则返回None
Try(aStr.toDouble).toOption.flatMap { aNum =>
Try(bStr.toDouble).toOption.map { bNum =>
(aNum, bNum) // 返回有效数字对
}
}
} else None
case _ => None // 处理null或无效配对
}
// 步骤2: 检查有效点数量(至少需要2个点计算相关系数)
if (validPairs.size < 2) {
null // 序列不足,返回null
} else {
// 步骤3: 计算均值
val xValues = validPairs.map(_._1)
val yValues = validPairs.map(_._2)
val meanX = xValues.sum / xValues.size
val meanY = yValues.sum / yValues.size
// 步骤4: 计算协方差和标准差
val cov = validPairs.map { case (xi, yi) => (xi - meanX) * (yi - meanY) }.sum
val stdDevXSq = validPairs.map { case (xi, _) => math.pow(xi - meanX, 2) }.sum
val stdDevYSq = validPairs.map { case (_, yi) => math.pow(yi - meanY, 2) }.sum
// 步骤5: 计算相关系数(处理分母为零的情况)
if (stdDevXSq == 0 || stdDevYSq == 0) {
null // 标准差为零,相关系数未定义
} else {
cov / (math.sqrt(stdDevXSq) * math.sqrt(stdDevYSq))
}
}
})
// 步骤4: 计算相关性
val resultCorrA = joinedDFA.withColumn("TRANS_CORRA", pearsonCorr($"CUSTARRAY", $"TRANSARRAY"))
.repartition(partitions).persist(StorageLevel.MEMORY_AND_DISK)
resultCorrA.show(false)
LeoUtils.logPrint(("resultCorrA", resultCorrA))
println(new Timestamp(System.currentTimeMillis()))
报错
Exception in thread "main" java.lang.UnsupportedOperationException: Schema for type Any is not supported
怎么处理
最新发布