"ORA-00923: FROM keyword not found where expected"

Question

我收到以下错误

执行查询时"ORA-00923：在预期位置找不到 FROM 关键字"

SELECT 
    count(pf.pref_selection) filter ( WHERE pc.collection = "players")      OVER (partition BY pc.collection,pc.user_id )  AS Avg_players        
    count(pf.pref_selection) filter ( WHERE pc.collection = "teams")        OVER (partition BY pc.collection,pc.user_id )  AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE>"30-SEP-16'`

Answer 1

您有几个错误。正如@cricket_007指出的那样，您在两个计数表达式之间缺少逗号。但是你的 filter 子句也有无效的语法 - 至少在 Oracle 中不存在;您在字符串文字周围使用了双引号而不是单引号;日期字符串周围的引号甚至更奇怪;并且您依赖于日期格式的 NLS 设置。

如果我了解您的尝试，这更接近您的需求：

SELECT 
    count(case when pc.collection = 'players' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_players, 
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      OVER (partition BY pc.user_id) AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'

不过，输出可能不是很有用。对于每个用户 ID，您可能会得到多行，每行显示总计数(由于某种原因您avg调用了该计数(。您可能只想查看每个 ID 的计数一次，并查看它们与哪个 ID 相关，因此我认为您想要聚合计数而不是分析计数：

SELECT 
    pc.user_id,
    count(case when pc.collection = 'players' then pf.pref_selection end)
      AS Avg_players,      
    count(case when pc.collection = 'teams' then pf.pref_selection end)
      AS Avg_teams  
FROM NBA_OWNER.PREFERENCES pf 
inner join NBA_OWNER.PREF_COLLECTIONS pc on pc.ID=pf.COLLECTION_ID 
where pc.LAST_UPDATE_DATE > date '2016-09-30'
group by pc.user_id