select substring(page_type,1,3) as page_type, count(*) as count_num, round((count(*)/771156.0)*100,4) as weights from law group by substring(page_type,1,3) order by count_num desc;改成sparksql

可以使用以下Spark SQL语句将原始SQL转换为Spark SQL：

SELECT SUBSTRING(page_type,1,3) AS page_type, COUNT(*) AS count_num, ROUND((COUNT(*)/771156.0)*100,4) AS weights 
FROM law 
GROUP BY SUBSTRING(page_type,1,3) 
ORDER BY count_num DESC;

注意，在Spark SQL中，字符串函数和数学函数的语法可能略有不同，因此需要适当调整原始SQL的语法。另外，如果需要在Spark SQL中读取数据，需要首先将数据加载到Spark中，例如使用Spark的DataFrame或Dataset API。

相关问题

select substring(page_type,1,6) as page_type, count(*) as count_num, round((count(*)/411665.0)*100,4) as weights from law_part where page_type_part=101 group by substring(page_type,1,6) order by count_num desc;改成sparksql

下面是将该SQL语句转换为Spark SQL的代码：

SELECT SUBSTRING(page_type,1,6) AS page_type, COUNT(*) AS count_num, ROUND((COUNT(*)/411665.0)*100,4) AS weights  
FROM law_part 
WHERE page_type_part=101  
GROUP BY SUBSTRING(page_type,1,6)  
ORDER BY count_num DESC;

转换为Spark SQL后的代码：

import org.apache.spark.sql.functions._

val resultDF = law_part
  .filter($"page_type_part" === 101)
  .groupBy(substring($"page_type", 1, 6).as("page_type"))
  .agg(count("*").as("count_num"))
  .withColumn("weights", round((col("count_num")/411665.0)*100, 4))
  .orderBy(desc("count_num"))

其中，`law_part`是你的数据表，可以通过`spark.read.jdbc()`或其他方式加载数据。

select substring(page_type,1,7) as page_type, visiturl, count(*) as count_num from law where visiturl like '%faguizt%' and page_type like '%199%' ;改成sparksql

可以将该SQL语句转换成Spark SQL语句如下：

SELECT SUBSTRING(page_type, 1, 7) AS page_type, visiturl, COUNT(*) AS count_num 
FROM law 
WHERE visiturl LIKE '%faguizt%' AND page_type LIKE '%199%'
GROUP BY SUBSTRING(page_type, 1, 7), visiturl;

在Spark SQL中，GROUP BY子句中的所有列都必须在SELECT子句中指定，因此需要将SUBSTRING(page_type, 1, 7)和visiturl都包含在SELECT子句中。