官术网_书友最值得收藏!

Using SQL subqueries

It is also possible to use subqueries in ApacheSparkSQL. In the following example, a SQL query uses an anonymous inner query in order to run aggregations on Windows. The encapsulating query is making use of the virtual/temporal result of the inner query, basically removing empty columns:

val result = spark.sql("""
SELECT * from (
SELECT
min(temperature) over w as min_temperature,
max(temperature) over w as max_temperature,
min(voltage) over w as min_voltage,
max(voltage) over w as max_voltage,
min(flowrate) over w as min_flowrate,
max(flowrate) over w as max_flowrate,
min(frequency) over w as min_frequency,
max(frequency) over w as max_frequency,
min(hardness) over w as min_hardness,
max(hardness) over w as max_hardness,
min(speed) over w as min_speed,
max(speed) over w as max_speed
FROM washing_flat
WINDOW w AS (ORDER BY ts ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING)
)
WHERE min_temperature is not null
AND max_temperature is not null
AND min_voltage is not null
AND max_voltage is not null
AND min_flowrate is not null
AND max_flowrate is not null
AND min_frequency is not null
AND max_frequency is not null
AND min_hardness is not null
AND min_speed is not null
AND max_speed is not null
""")

The result of the subqueries is as follows:

主站蜘蛛池模板: 娱乐| 浠水县| 阿城市| 西和县| 乐业县| 阳信县| 东至县| 鄂尔多斯市| 平原县| 墨江| 莱芜市| 甘肃省| 昌邑市| 彰化市| 巫溪县| 五常市| 铜陵市| 儋州市| 门源| 滨海县| 荥经县| 印江| 白水县| 桐庐县| 嘉禾县| 怀宁县| 慈溪市| 鄂托克前旗| 青浦区| 临湘市| 花垣县| 册亨县| 渭南市| 年辖:市辖区| 吕梁市| 六安市| 玉山县| 涿鹿县| 同心县| 江北区| 泰州市|