Powershell: PSSQLITE: Complex SQL not supported

Question

Powershell版本:5.1PSSQLITE版本:1.1.0

我有一个SQL运行在SQLITE。

1. 如果我连接松鼠SQL，它工作得很好
2. 如果我在PS中运行PSSQLIte，错误弹出如下:

Invoke-SqliteQuery : Exception calling "Fill" with "1" argument(s): "SQL logic error or missing database
near "(": syntax error"
At line:11 char:8
+ return Invoke-SqliteQuery -DataSource $sourceDB -Query $sql
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Invoke-SqliteQuery

这是否意味着pssqlite对SQL的支持有限?

以下是我的完整代码供您参考:Create Table

CREATE TABLE REVIEW_LOGS(
NUMBER TEXT,
TYPE TEXT,
DATE TEXT,
Reviewee TEXT,
Reviewer TEXT,
TICKET_NUMBER TEXT,
SHORT_DESCRIPTION TEXT,
DURATION INTEGER,
TOPICS_COVERED TEXT,
OUTPUT TEXT,
TO_BE_IMPROVED TEXT,
ACTION_ITEM TEXT,
Adjusted_Issue_Communication TEXT,
ASAT TEXT default current_timestamp
)   

Execute SQL in SQLITE

function execute-sql
{
Param(
[Parameter(Mandatory=$true)]
[String]$sql,
[String]$sourceDB = 'c:datacwdticketlogs.db'             
)

try{
$sql |out-file tttt.log -append
return Invoke-SqliteQuery -DataSource $sourceDB -Query $sql 
}catch {
Write-Host $sql
}

}

问题SQL

SELECT Metric, count, percentage FROM
(
SELECT Metric, count, percentage, rank() over(PARTITION  by Metric order by count desc) as rank
FROM(
select Adjusted_Issue_Communication as Metric, count(*)as count , 
round(count(*)*1.0/(select count(*) from review_logs where date between '2021-01-01'  and '2021-01-31' )*100, 2)|| '%' as percentage
from review_logs where date between '2021-01-01' and '2021-01-31' group by 1 
Union
SELECT 'N/A'  as Metric, 0 as count, "0.0%" as percentage 
Union
SELECT 'Yes'  as Metric, 0 as count, "0.0%" as percentage 
Union
SELECT 'No'  as Metric, 0 as count, "0.0%" as percentage 
)
)where rank = 1
order by Metric;

Answer 1

我也尝试使用rank()，但失败了。发现这个问题记录在PSSQLite中，说明PS5版本使用旧的sqlite二进制文件，不包括排名和其他分析功能，但PS Core二进制文件已更新。我在Powershell 7.1.2版本中测试过，同样的命令在PS 5.1中失败

Invoke-SqliteQuery -DataSource .email.db -Query 'select rank() over (partition by Name order by name) as Ranks from email;'

可以在Powershell 7.1.2中使用