替换clickhouse中的行号()

lhcgjxsq 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(4)|浏览(1259)

clickhouse数据库不支持行号（），正在寻找替代函数。

SELECT company_name AS company,
       DOMAIN,
       city_name AS city,
       state_province_code AS state,
       country_code AS country,
       location_revenue AS revenueRange,
       location_TI_industry AS industry,
       location_employeecount_range AS employeeSize,
       topic,
       location_duns AS duns,
       rank AS intensityRank,
       dnb_status_code AS locationStatus,
       rank_delta AS intensityRankDelta,
       company_id,
       ROW_NUMBER() OVER (PARTITION BY DOMAIN) AS rowNumberFROM company_intent c
 WHERE c.rank > 0
   AND c.rank <= 10
   AND c.signal_count > 0
   AND c.topic IN ('Cloud Computing')
   AND c.country_code = 'US'
   AND c.rank IN (7, 8, 9, 10)
 GROUP BY c.location_duns,
          company_name,
          DOMAIN,
          city_name,
          state_province_code,
          country_code,
          location_revenue,
          location_TI_industry,
          location_employeecount_range,
          topic,
          rank,
          dnb_status_code,
          rank_delta,
          company_id
 ORDER BY intensityRank DESC
 LIMIT 15 SELECT COUNT (DISTINCT c.company_id) AS COUNT
  FROM company_intent c
 WHERE c.rank > 0
   AND c.rank <= 10
   AND c.signal_count > 0
   AND c.topic IN ('Cloud Computing')
   AND c.country_code = 'US'
   AND c.rank IN (7, 8, 9, 10)

执行上述查询时，出现以下错误。
应为以下之一：设置、格式、with、having、limit、from、prewhere、token、union all、逗号、where、order by、into outfile、group by
如有任何建议，我们将不胜感激

sql clickhouse yandex

来源：https://stackoverflow.com/questions/53757415/replacement-for-row-number-in-clickhouse

4条答案

按热度按时间

m4pnthwp1#

clickhouse目前不支持窗口功能。有一个rownumberinallblocks函数，您可能会感兴趣。

赞(0）回复(0）举报 2021-07-15

pw9qyyiw2#

SELECT
    *,
    rowNumberInAllBlocks()
FROM
    (
        -- YOUR SELECT HERE
    )

https://clickhouse-docs.readthedocs.io/en/latest/functions/other_functions.html 说：
rownumberinallblocks（）返回此函数处理的所有块中的增量行号。

赞(0）回复(0）举报 2021-07-15

zxlwwiss3#

像这样的smth（很糟糕，但效果很好）

SELECT *, rn +1 -min_rn current, max_rn - min_rn + 1 last FROM (
SELECT *, rowNumberInAllBlocks() rn FROM (
SELECT i_device, i_time
FROM tbl
ORDER BY i_device, i_time
) t
) t1 LEFT JOIN (
SELECT i_device, min(rn) min_rn, max(rn) max_rn FROM (
SELECT *, rowNumberInAllBlocks() rn FROM (
SELECT i_device, i_time
FROM tbl
ORDER BY i_device, i_time
) t
) t GROUP BY i_device
) t2 USING (i_device)

赞(0）回复(0）举报 2021-07-15

unftdfkk4#

SELECT *, rowNumberInAllBlocks() as row_count FROM (SELECT .....)

赞(0）回复(0）举报 2021-07-15

我来回答

替换clickhouse中的行号()

4条答案

相关问题

热门标签

最新问答