按clickhouse中的操作员无顺序获取前n行

vmdwslir 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(2)|浏览(668)

我有一张table

CREATE TABLE StatsFull (
  Timestamp Int32,
  Uid String,
  ErrorCode Int32,
  Name String,
  Version String,
  Date Date MATERIALIZED toDate(Timestamp),
  Time DateTime MATERIALIZED toDateTime(Timestamp)
) ENGINE = MergeTree() PARTITION BY toMonday(Date)
ORDER BY Time SETTINGS index_granularity = 8192

我需要用唯一的UID或者前100个错误代码来获得前100个名字。
显而易见的疑问是

SELECT Name, uniq(PcId) as cnt FROM StatsFull
WHERE Time > subtractDays(toDate(now()), 1)
GROUP BY Name ORDER BY cnt DESC LIMIT 100

但是数据太大了，所以我创建了一个aggregatingmergetree，因为我不需要按小时（仅按日期）过滤数据。

CREATE MATERIALIZED VIEW StatsAggregated (
  Date Date,
  ProductName String,
  ErrorCode Int32,
  Name String,
  Version String,
  UniqUsers AggregateFunction(uniq, String),
) ENGINE = AggregatingMergeTree() PARTITION BY toMonday(Date)
ORDER BY
  (
    Date,
    ProductName,
    ErrorCode,
    Name,
    Version
  ) SETTINGS index_granularity = 8192 AS
SELECT
  Date,
  ProductName,
  ErrorCode,
  Name,
  Version,
  uniqState(Uid) AS UniqUsers,
FROM
  StatsFull
GROUP BY
  Date,
  ProductName,
  ErrorCode,
  Name,
  Version

我现在的问题是：

SELECT Name FROM StatsAggregated 
WHERE Date > subtractDays(toDate(now()), 1)
GROUP BY Name
ORDER BY uniqMerge(UniqUsers) DESC LIMIT 100

查询运行得很好，但是最终一天中的数据行变得越来越多，现在它太贪心内存了。所以我在寻找一些优化。
我找到了函数topk（n）（column），它返回指定列中最频繁值的数组，但这不是我需要的。

clickhouse

来源：https://stackoverflow.com/questions/59500560/get-top-n-rows-without-order-by-operator-in-clickhouse

2条答案

按热度按时间

plupiseo1#

我想建议以下几点：
在可能的情况下，最好使用simpleaggregatefunction而不是aggregatefunction
与uniq相比，使用uniqcombined/uniqcombined64“占用的内存要少几倍”
减少聚合视图中的维度数（似乎可以省略productname和版本）

CREATE MATERIALIZED VIEW StatsAggregated (
  Date Date,
  Name String,
  ErrorCode Int32
  UniqUsers AggregateFunction(uniq, String),
) ENGINE = AggregatingMergeTree()
PARTITION BY toMonday(Date)
ORDER BY (Date, Name, ErrorCode) AS
SELECT Date, Name, ErrorCode, uniqState(Uid) AS UniqUsers,
FROM StatsFull
GROUP BY Date, Name, ErrorCode;

向结果查询的when子句添加额外的“启发式”约束

SELECT Name, uniqMerge(UniqUsers) uniqUsers 
FROM StatsAggregated 
WHERE Date > subtractDays(toDate(now()), 1)
  AND uniqUsers > 12345 /* <-- 12345 is 'heuristic' number that you evaluate based on your data */
  AND ErrorCode = 0 /* apply any other conditions to narrow the result set as short as possible */
GROUP BY Name
ORDER BY uniqUsers DESC LIMIT 100

使用采样

/* Raw-table */

CREATE TABLE StatsFull (
 /* .. */
) ENGINE = MergeTree() 
PARTITION BY toMonday(Date)
SAMPLE BY xxHash32(Uid) /* < -- */
ORDER BY Time, xxHash32(Uid)

/* Applying sampling to raw-table can make faster the short-term queries (period in several hours etc) */

SELECT Name, uniq(PcId) as cnt 
FROM StatsFull
SAMPLE 0.05 /* <-- */
WHERE Time > subtractHours(now(), 6) /* <-- hours-period */
GROUP BY Name 
ORDER BY cnt DESC LIMIT 100

/* Aggregated-table */

CREATE MATERIALIZED VIEW StatsAggregated (
  Date Date,
  ProductName String,
  ErrorCode Int32,
  Name String,
  Version String,
  UniqUsers AggregateFunction(uniq, String),
) ENGINE = AggregatingMergeTree() 
PARTITION BY toMonday(Date)
SAMPLE BY intHash32(toInt32(Date)) /* < -- not sure that is good to choose */
ORDER BY (intHash32(toInt32(Date)), ProductName, ErrorCode, Name, Version)
SELECT /* .. */ FROM StatsFull GROUP BY /* .. */**

/* Applying sampling to aggregated-table can make faster the long-term queries (period in several weeks, months etc) */

SELECT Name 
FROM StatsAggregated 
SAMPLE 0.1 /* < -- */
WHERE Date > subtractMonths(toDate(now()), 3) /* <-- months-period */
GROUP BY Name
ORDER BY uniqMerge(UniqUsers) DESC LIMIT 100

使用分布式查询处理。将数据分成若干部分（碎片）可以进行分布式处理；处理性能的额外提高提供了使用分布式\u group \u by \u no \u合并查询设置。

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

wqsoz72f2#

如果需要将数组转置到行，可以使用arrayjoin

SELECT Name, arrayJoin(topK(100)(Count)) AS top100_Count FROM Stats

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

我来回答

按clickhouse中的操作员无顺序获取前n行

2条答案

相关问题

热门标签

最新问答