gp数据库---子查询报错 correlated subquery with skip-level correlations is not supported

最新推荐文章于 2021-08-23 17:26:13 发布
最新推荐文章于 2021-08-23 17:26:13 发布
阅读量3.4k 收藏 1
点赞数 2
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/IcyWang1/article/details/102967822
版权
在使用Greenplum数据库时，通过JDBC执行SQL遇到子查询报错，问题在于子查询中存在跨级引用主表字段。解决方法是避免这种skip-level correlations，将复杂的子查询拆分为多个独立查询。示例展示了原SQL与修改后的SQL对比，修改后的SQL移除了内部子查询，成功在数据库和代码中执行。
摘要由CSDN通过智能技术生成

gp数据库（greenPlum）代码jdbc执行sql报错信息：

SQL state [0A000]; error code [0]; [Pivotal][Greenplum JDBC Driver][Greenplum]correlated subquery with skip-level correlations is not supported. ; nested exception is java.sql.SQLException: [Pivotal][Greenplum JDBC Driver][Greenplum]correlated subquery with skip-level correlations is not supported.

问题现象：
要查询原sql在库里可以执行，但是代码jdbc执行会报错。
sql如下：

select
    (select
        CASE
            WHEN type = '12'
            THEN (select a.area_id from dim.dim_area_relation a where a.utrancell_district_name=district_name)
            WHEN type = '13'
            THEN (select a.parent_area_id from dim.dim_area_relation a where a.utrancell_district_name=district_name)
            ELSE '其他'
        END DISTRICT_ID
    from  dim.dim_area_relation a
    wh
最低0.47元/天 解锁文章
IcyWang1
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
出现 nested exception is java.sql.SQLException: 无效的列类型 的解决方法
码农研究僧的博客
06-29 269
SQL state [99999]; error code [17004]; 无效的列类型; nested exception is java.sql.SQLException: 无效的列类型
ERROR: correlated subquery with skip-level correlations is not supported
霸王龙i的博客
07-27 1252
Version：PostgreSQL 8.3.23 (Greenplum Database 5.10.2） 在stackoverflow找到了答案↓ https://stackoverflow.com/questions/36470326/sql-query-error-correlated-subquery-with-skip-level-correlations-is-not-suppor...
关于关联子查询--correlated subquery
甲骨文可乐的专栏
07-08 2772
文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries007.htm#sthref3193   Using Subqueries        Oracle performs a correlated subquery when a nested subquery references a column from a t
Correlated Subqueries
03-13 149
correlated subquery is a subquery (a query nested inside another query) that uses values from the outer query in i...
关联子查询 correlated sub-queries
Cecilia_W422的博客
03-23 620
对于数据量多的肯定是用连接查询快些，原因：因为子查询会多次遍历所有的数据（视你的子查询的层次而定），而连接查询只会遍历一次。 但是数据量少的话也就无所谓是连接查询还是子查询，视自己的习惯而定。一般情况下还是用子查询来的好，容易控制。
mysql函数取代相关子查询(Correlated subquery)
大神带我来搬砖
11-05 876
[code="sql"]CREATE TABLE `20121105_teacher` ( `teacher_id` int(11) NOT NULL, `school_id` int(11) NOT NULL, PRIMARY KEY (`teacher_id`), KEY `20121105_teacher_idx_school` (`school_id`) ) EN...
Bad Luck on Subquery
09-25 87
面试碰到这样一道SQL题: 数据库结构如图 要求取出 1. Country Name 2. 在这个Ｃｏｕｎｔｒｙ的city count 3. 在这个Ｃｏｕｎｔｒｙ的hotel count 当时一想后面题还多着呢，　祭出Ｓｕｂｑｕｅｒｙ　三下五除二，　搞定，　 代码 SELECT Country.Name, (SELECT 　　　　　...
Optimal sequential Kalman filtering with cross-correlated measurement noises
02-20
The purpose of this paper is to present a new optimal sequential decentralized filtering algorithm for discrete time-varying linear control systems with cross-correlated noises. The new method uses a ...
spatially correlated Gnoise_2d-music_ula_bistaticradar_
09-28
标题中的"spatially correlated Gnoise_2d-music_ula_bistaticradar_"提到了几个关键概念，让我们逐一解析。首先，"spatially correlated Gnoise"指的是空间相关的广义噪声，这在雷达信号处理中是重要的考虑因素。在...
Efficientsimulationofspace-timecorrelatedIMOmobile_MIMO channel_
07-15
Efficient simulation of space-time correlated MIMO mobile fading channels
管理多个研究项目-研究论文
06-10
在标准实验问题中，决策者一次最多只能对一个项目进行实验。 如果我们让他们同时进行多个项目的试验，会不会有好处？ 我们表明答案是肯定的，即使知道只有一个项目在事后是富有成效的。 我们考虑两个相互冲突的项目...
greenplum.jar 官方JDBC驱动
06-29
public static void testGreenplum() { try { Class.forName("com.pivotal.jdbc.GreenplumDriver"); Connection db = DriverManager .getConnection( "jdbc:pivotal:greenplum://xxx:5432;DatabaseName=core_db", "gpadmin", "gpadmin"); Statement st = db.createStatement(); ResultSet rs = st .executeQuery("SELECT * FROM core_db.analysis_user limit 100"); while (rs.next()) { System.out.println(rs.getString(1)); } rs.close(); st.close(); } catch (Exception e) { e.printStackTrace(); } }
greenplum.jar 官方驱动 JDBC
11-16
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Greenplum { public static void main(String args[]) throws Exception{ Class.forName("com.pivotal.jdbc.GreenplumDriver"); Connection db = DriverManager.getConnection("jdbc:pivotal:greenplum://192.168.139.128:5432;DatabaseName=postgres", "gpadmin", "gpadmin"); Statement st = db.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM test01 limit 100"); while (rs.next()) { System.out.println(rs.getString(1)); } rs.close(); st.close(); } }
通过相关廉价谈话实现沟通均衡：两人案例-研究论文
06-10
我们表明，本质上，任何有两个玩家的有限贝叶斯博弈的每个交流均衡都可以作为扩展博弈的战略形式相关均衡来实现，其中在贝叶斯博弈中选择行动之前，玩家参与一个可能无限长的（但在均衡中几乎肯定是有限的），直接的...
相关子查询（correlated subquery） 与非相关子查询
gxftry1st的专栏
03-19 1843
1.概念：   子查询分为两种类型：独立子查询和相关子查询      ①独立子查询是单独执行，其结果参与外层的查询，         整个查询中子查询只执行一次，执行完后再执行外层查询；      ②相关子查询是指子查询(内层查询)中引用了外层查询所引用表的字段，         因此外层查询处理每一条记录时都必须执行一次子查询，         因为子查询中引用的字段的值发生了变化。
correlated subquery and non-correlated subquery
Watson的专栏
04-09 2021
子查询：嵌套在其他查询中的查询称之。 子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。 所有的子查询可以分为两类，即相关子查询和非相关子查询 1>非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。 2>相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。 故非相关子查询比相关子查询效率高 查询工资大于所有员工
MySQL|相关子查询 Correlated subqueries
5年+算法工程师，分享人工智能前沿技术，与自己的工程经验，欢迎订阅关注。
04-19 163
--Select employees whose salary is above the average in their office -- GET invoices that are larger than the client's average invoice amount SELECT * FROM invoices i WHERE invoice_total > ( SELECT AVG(invoice_total) FROM invoices WHERE clien
Greenplum【问题 01】报错 could not determine data type of parameter $2. 问题解决（踩坑分享）
Java与大数据相关实践内容分享！
08-23 4403
Greenplum【问题 01】报错 could not determine data type of parameter $2. 问题解决（踩坑分享）
SQL state [null]; error code [0]; connection holder is null
热门推荐
蓝天之枫
01-16 1万+
线上环境凌晨5分有个定时任务执行了，但是执行过程中有报错，导致数据全部回滚，报错信息如下： ### Cause: java.sql.SQLException: connection holder is null ; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; connection holder...
python 代码实现Uplift model for multiple correlated binary responses WITH LOW-RANK REGULARIZATION logistic regression
最新发布
06-08
Sure, here is an example Python code for Uplift model with low-rank regularization logistic regression for multiple correlated binary responses: ```python import numpy as np from sklearn.linear_model import LogisticRegression # define the Uplift model with low-rank regularization logistic regression class UpliftLowRankLogisticRegression: def __init__(self, rank=5, alpha=1.0): self.rank = rank self.alpha = alpha self.models = [] self.w = None def fit(self, X, y, t): # calculate the treatment and control groups X_treatment = X[t == 1] y_treatment = y[t == 1] X_control = X[t == 0] y_control = y[t == 0] # fit the logistic regression model for each response for i in range(y.shape[1]): model = LogisticRegression(penalty='l2', C=self.alpha) model.fit(np.hstack((X_treatment, y_treatment[:, i].reshape(-1, 1))), y_treatment[:, i]) self.models.append(model) # use SVD to learn the low-rank representation w U, S, Vt = np.linalg.svd(y_control - self.predict(X_control)) self.w = Vt[:self.rank].T def predict(self, X): # calculate the uplift score for each response uplift_scores = np.zeros((X.shape[0], len(self.models))) for i, model in enumerate(self.models): uplift_scores[:, i] = model.predict_proba(X)[:, 1] # calculate the predicted response for the control group y_control_pred = np.dot(X, self.w) # calculate the predicted response for the treatment group y_treatment_pred = y_control_pred + uplift_scores # return the predicted response matrix return np.vstack((y_control_pred, y_treatment_pred)) ``` The `UpliftLowRankLogisticRegression` class takes two hyperparameters: `rank` for the rank of the low-rank representation w and `alpha` for the regularization strength of logistic regression. In the `fit` method, the treatment and control groups are separated, and logistic regression models are fitted for each response using the treatment group. Then, SVD is used to learn the low-rank representation w from the predicted responses of the control group. In the `predict` method, the uplift scores for each response are calculated using the logistic regression models and added to the predicted responses of the control group to obtain the predicted responses of the treatment group. The predicted response matrix is returned by stacking the predicted responses of the control and treatment groups vertically.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值