Linear regression in mysql

本文介绍如何使用SQL聚合函数来计算两组变量之间的最小二乘回归线及相关系数,通过具体实例展示了计算过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

From: http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-13-sect-6.html

 

13.6 Calculating Linear Regressions or Correlation Coefficients

13.6.1 Problem

You want to calculate the least-squares regression line for two variables, or the correlation coefficient that expresses the strength of the relationship between them.

13.6.2 Solution

Apply summary functions to calculate the necessary terms.

13.6.3 Discussion

When the data values for two variables X and Y are stored in a database, the least-squares regression for them can be calculated easily using aggregate functions. The same is true for the correlation coefficient. The two calculations are actually fairly similar, and many terms for performing the computations are common to the two procedures.

Suppose you want to calculate a least-squares regression using the age and test score values for the observations in the testscore table:

mysql> SELECT age, score FROM testscore;

+-----+-------+
| age | score |
+-----+-------+
| 5 | 5 |
| 5 | 4 |
| 5 | 6 |
| 5 | 7 |
| 6 | 8 |
| 6 | 9 |
| 6 | 4 |
| 6 | 6 |
| 7 | 8 |
| 7 | 6 |
| 7 | 9 |
| 7 | 7 |
| 8 | 9 |
| 8 | 6 |
| 8 | 7 |
| 8 | 10 |
| 9 | 9 |
| 9 | 7 |
| 9 | 10 |
| 9 | 9 |
+-----+-------+

A regression line is expressed as follows, where a and b are the intercept and slope of the line:

Y = bX + a

Letting age be X and score be Y, begin by computing the terms needed for the correlation equation. These include the number of observations, the means, sums, and sums of squares for each variable, and the sum of the products of each variable:[2]

[2] You can see where these terms come from by consulting any standard statistics text.

mysql> SELECT

-> @n := COUNT(score) AS N,
-> @meanX := AVG(age) AS "X mean",
-> @sumX := SUM(age) AS "X sum",
-> @sumXX := SUM(age*age) "X sum of squares",
-> @meanY := AVG(score) AS "Y mean",
-> @sumY := SUM(score) AS "Y sum",
-> @sumYY := SUM(score*score) "Y sum of square",
-> @sumXY := SUM(age*score) AS "X*Y sum"
-> FROM testscore/G
*************************** 1. row ***************************
N: 20
X mean: 7.0000
X sum: 140
X sum of squares: 1020
Y mean: 7.3000
Y sum: 146
Y sum of square: 1130
X*Y sum: 1053

From those terms, the regression slope and intercept are calculated as follows:

mysql> SELECT

-> @b := (@n*@sumXY - @sumX*@sumY) / (@n*@sumXX - @sumX*@sumX)
-> AS slope;
+-------+
| slope |
+-------+
| 0.775 |
+-------+
mysql> SELECT @a :=
-> (@meanY - @b*@meanX)
-> AS intercept;
+-----------+
| intercept |
+-----------+
| 1.875 |
+-----------+

The regression equation then is:

mysql> SELECT CONCAT('Y = ',@b,'X + ',@a) AS 'least-squares regression';

+--------------------------+
| least-squares regression |
+--------------------------+
| Y = 0.775X + 1.875 |
+--------------------------+

To compute the correlation coefficient, many of the same terms are used:

mysql> SELECT

-> (@n*@sumXY - @sumX*@sumY)
-> / SQRT((@n*@sumXX - @sumX*@sumX) * (@n*@sumYY - @sumY*@sumY))
-> AS correlation;
+------------------+
| correlation |
+------------------+
| 0.61173620442199 |
+------------------+

# coding: utf-8 import json from flask import current_app as app from pyspark.ml.classification import LogisticRegression from pyspark.ml.clustering import KMeans from pyspark.ml.feature import VectorAssembler from pyspark.ml.regression import LinearRegression from pyspark.sql import SparkSession def spark_read_mysql(sql, json_filename): ''' 排序 :param sql: :param json_filename: :return: ''' df = app.spark.read.format("jdbc").options(url=app.jdbc_url, dbtable=sql).load() count = df.count() df_data = df.toPandas().to_dict() json_data = [] for i in range(count): temp = {} for k, v in df_data.items(): temp[k] = v.get(i) json_data.append(temp) with open(json_filename, 'w', encoding='utf-8') as f: f.write(json.dumps(json_data, indent=4, ensure_ascii=False)) def linear(table_name): ''' 回归 :param table_name: :return: ''' spark = SparkSession.builder.appName("flask").getOrCreate() training = spark.read.format("libsvm").table(table_name) lr = LinearRegression(maxIter=20, regParam=0.01, elasticNetParam=0.6) lrModel = lr.fit(training) trainingSummary = lrModel.summary print("numIterations: %d" % trainingSummary.totalIterations) print("objectiveHistory: %s" % str(trainingSummary.objectiveHistory)) trainingSummary.residuals.show() print("RMSE: %f" % trainingSummary.rootMeanSquaredError) print("r2: %f" % trainingSummary.r2) result = trainingSummary.residuals.toJSON() spark.stop() return result def cluster(table_name): ''' 聚类 :param table_name: :return: ''' spark = SparkSession.builder.appName("flask").getOrCreate() dataset = spark.read.format("libsvm").table(table_name) kmeans = KMeans().setK(2).setSeed(1) model = kmeans.fit(dataset) centers = model.clusterCenters() for center in centers: print(center) return centers def selector(table_name, Cols)spark在该项目中起到的作用
04-01
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值