如何为私有大语言模型快速沉淀高质量数据集

## InstructionGenerate a dataset## Input DataGive me %d specific cases of Clickhouse high-performance query scenarios, it should be returned in a List.Every case item contains 3 fields, field "schema" is the table structure(CREATE TABLE SQL), field "question" is the natural language question, and field "query" is the SQL query for the question.The query need to include a combination of the following Clickhouse capabilities:  a. Clickhouse functions like rank、dense_rank,、row_number、stochasticLogisticRegression、evalMLMethod、stochasticLinearRegression、arrayPopBack、arrayPopFront、arrayPushBack、arrayPushFront、arrayResize、arraySlice、arrayJoin、arrayDifference、arrayDistinct、tumble、hop、tumbleStart等Time Window、L1Norm、L2Norm、LinfNorm  b. Use Aggregate functions such as group by, ROLL UP, CUBE, etc.  c. Use Where and Pre Where to filter time, product, etc.  d. Use select syntax such as With, Sample, OFFSET, LIMIT, etc.  e. You can build complex scenarios with multiple with clauses / sub queries  f. Please make sure it is the best in terms of performance## Output IndicatorPure JSON Format string with no quotes, no explanations, no self-reference, no apologies, just answer.For example, this is a dataset contains 2 cases:` ``` `[  {    "schema": "CREATE TABLE IF NOT EXISTS product_recommendation_model( customer_id UInt32, recommendation Array(UInt32), PRIMARY KEY (customer_id)) ENGINE = MergeTree() ORDER BY customer_id;",    "question": "Recommend 5 products with similar purchase history to customer 123.",    "query": "WITH( SELECT customer_id, recommendation, row_number() OVER ( ORDER BY L2Norm(recommendation) DESC) AS row_num FROM ( SELECT customer_id, arrayMap( (i, score) -> IF(score > 0.5, i, 0), arrayEnumerate(predictor) ) AS recommendationFROM customer_product_stateWHERE date >= '2022-01-01' AND date <= '2022-02-01' AND customer_id = 123 ) GROUP BY customer_id, recommendation ) AS recommendation_ranksINSERT INTO product_recommendation_model (customer_id, recommendation) SELECT customer_id, recommendationFROM recommendation_ranksWHERE row_num <= 5;"  },  {    "schema": "CREATE OR REPLACE TABLE hits(WatchID BIGINT, JavaEnable SMALLINT, Title TEXT, GoodEvent SMALLINT, EventTime TIMESTAMP, EventDate Date, CounterID INTEGER, ClientIP INTEGER, RegionID INTEGER, UserID BIGINT, CounterClass SMALLINT, OS SMALLINT, UserAgent SMALLINT, URL TEXT, Referer TEXT, IsRefresh SMALLINT, RefererCategoryID SMALLINT, RefererRegionID INTEGER, URLCategoryID SMALLINT, URLRegionID INTEGER, ResolutionWidth SMALLINT, ResolutionHeight SMALLINT, ResolutionDepth SMALLINT, FlashMajor SMALLINT, FlashMinor SMALLINT, FlashMinor2 TEXT, NetMajor SMALLINT, NetMinor SMALLINT, UserAgentMajor SMALLINT, UserAgentMinor VARCHAR(255), CookieEnable SMALLINT, JavascriptEnable SMALLINT, IsMobile SMALLINT, MobilePhone SMALLINT, MobilePhoneModel TEXT, Params TEXT, IPNetworkID INTEGER, TraficSourceID SMALLINT, SearchEngineID SMALLINT, SearchPhrase TEXT, AdvEngineID SMALLINT, IsArtifical SMALLINT, WindowClientWidth SMALLINT, WindowClientHeight SMALLINT, ClientTimeZone SMALLINT, ClientEventTime TIMESTAMP, SilverlightVersion1 SMALLINT, SilverlightVersion2 SMALLINT, SilverlightVersion3 INTEGER, SilverlightVersion4 SMALLINT, PageCharset TEXT, CodeVersion INTEGER, IsLink SMALLINT, IsDownload SMALLINT, IsNotBounce SMALLINT, FUniqID BIGINT, OriginalURL TEXT, HID INTEGER, IsOldCounter SMALLINT, IsEvent SMALLINT, IsParameter SMALLINT, DontCountHits SMALLINT, WithHash SMALLINT, HitColor CHAR, LocalEventTime TIMESTAMP, Age SMALLINT, Sex SMALLINT, Income SMALLINT, Interests SMALLINT, Robotness SMALLINT, RemoteIP INTEGER, WindowName INTEGER, OpenerName INTEGER, HistoryLength SMALLINT, BrowserLanguage TEXT, BrowserCountry TEXT, SocialNetwork TEXT, SocialAction TEXT, HTTPError SMALLINT, SendTiming INTEGER, DNSTiming INTEGER, ConnectTiming INTEGER, ResponseStartTiming INTEGER, ResponseEndTiming INTEGER, FetchTiming INTEGER, SocialSourceNetworkID SMALLINT, SocialSourcePage TEXT, ParamPrice BIGINT, ParamOrderID TEXT, ParamCurrency TEXT, ParamCurrencyID SMALLINT, OpenstatServiceName TEXT, OpenstatCampaignID TEXT, OpenstatAdID TEXT, OpenstatSourceID TEXT, UTMSource TEXT, UTMMedium TEXT, UTMCampaign TEXT, UTMContent TEXT, UTMTerm TEXT, FromTag TEXT, HasGCLID SMALLINT, RefererHash BIGINT, URLHash BIGINT, CLID INTEGER) ENGINE = MergeTree() ORDER BY (CounterID, EventDate, UserID, EventTime, WatchID)",    "question": "give me page view count bucketed by date and order by date between 2013-07-14 and 2013-07-15 where CounterID is 62 and is not refreshed",    "query": "SELECT DATE_TRUNC('day', EventTime) AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 GROUP BY DATE_TRUNC('day', EventTime) ORDER BY DATE_TRUNC('day', EventTime)"  }]` ``` `

client := openai.NewClient(token)

// Create a new instance of the CompletionRequest struct// Set up the chat parameters(<https://platform.openai.com/docs/api-reference/completions>)params := openai.ChatCompletionRequest{  Model: openai.GPT3Dot5Turbo,  Messages: append(messages, openai.ChatCompletionMessage{    Role:    openai.ChatMessageRoleUser,    Content: newPrompt,  }),  MaxTokens:   DefaultMaxTokens,  Temperature: DefaultTemperature,}response, err = client.CreateChatCompletion(ctx, params)if err != nil {  err = fmt.Errorf("[Chat] failed to create chat completion: %w", err)}

type (  AiSQLData struct {    Datasource string `json:"datasource"`​    // input part of text-to-sql model    Question string `json:"question"`    Schema   string `json:"schema"`​    // output part of text-to-sql model    AiSQLOutput  }​  AiSQLOutput struct {    Query    string  `json:"query"`    Analysis string  `json:"analysis"`    Table    [][]any `json:"table"`  })

## InstructionShow me the steps to get the provided Query## ContextI've got a Query SQL based on a schema(CREATE TABLE SQL) and a specific task## Input Dataschema: "CREATE OR REPLACE TABLE hits(WatchID BIGINT, JavaEnable SMALLINT, Title TEXT, GoodEvent SMALLINT, EventTime TIMESTAMP, EventDate Date, CounterID INTEGER, ClientIP INTEGER, RegionID INTEGER, UserID BIGINT, CounterClass SMALLINT, OS SMALLINT, UserAgent SMALLINT, URL TEXT, Referer TEXT, IsRefresh SMALLINT, RefererCategoryID SMALLINT, RefererRegionID INTEGER, URLCategoryID SMALLINT, URLRegionID INTEGER, ResolutionWidth SMALLINT, ResolutionHeight SMALLINT, ResolutionDepth SMALLINT, FlashMajor SMALLINT, FlashMinor SMALLINT, FlashMinor2 TEXT, NetMajor SMALLINT, NetMinor SMALLINT, UserAgentMajor SMALLINT, UserAgentMinor VARCHAR(255), CookieEnable SMALLINT, JavascriptEnable SMALLINT, IsMobile SMALLINT, MobilePhone SMALLINT, MobilePhoneModel TEXT, Params TEXT, IPNetworkID INTEGER, TraficSourceID SMALLINT, SearchEngineID SMALLINT, SearchPhrase TEXT, AdvEngineID SMALLINT, IsArtifical SMALLINT, WindowClientWidth SMALLINT, WindowClientHeight SMALLINT, ClientTimeZone SMALLINT, ClientEventTime TIMESTAMP, SilverlightVersion1 SMALLINT, SilverlightVersion2 SMALLINT, SilverlightVersion3 INTEGER, SilverlightVersion4 SMALLINT, PageCharset TEXT, CodeVersion INTEGER, IsLink SMALLINT, IsDownload SMALLINT, IsNotBounce SMALLINT, FUniqID BIGINT, OriginalURL TEXT, HID INTEGER, IsOldCounter SMALLINT, IsEvent SMALLINT, IsParameter SMALLINT, DontCountHits SMALLINT, WithHash SMALLINT, HitColor CHAR, LocalEventTime TIMESTAMP, Age SMALLINT, Sex SMALLINT, Income SMALLINT, Interests SMALLINT, Robotness SMALLINT, RemoteIP INTEGER, WindowName INTEGER, OpenerName INTEGER, HistoryLength SMALLINT, BrowserLanguage TEXT, BrowserCountry TEXT, SocialNetwork TEXT, SocialAction TEXT, HTTPError SMALLINT, SendTiming INTEGER, DNSTiming INTEGER, ConnectTiming INTEGER, ResponseStartTiming INTEGER, ResponseEndTiming INTEGER, FetchTiming INTEGER, SocialSourceNetworkID SMALLINT, SocialSourcePage TEXT, ParamPrice BIGINT, ParamOrderID TEXT, ParamCurrency TEXT, ParamCurrencyID SMALLINT, OpenstatServiceName TEXT, OpenstatCampaignID TEXT, OpenstatAdID TEXT, OpenstatSourceID TEXT, UTMSource TEXT, UTMMedium TEXT, UTMCampaign TEXT, UTMContent TEXT, UTMTerm TEXT, FromTag TEXT, HasGCLID SMALLINT, RefererHash BIGINT, URLHash BIGINT, CLID INTEGER) ENGINE = MergeTree() ORDER BY (CounterID, EventDate, UserID, EventTime, WatchID)"question: "give me page view count bucketed by date and order by date between 2013-07-14 and 2013-07-15 where CounterID is 62 and is not refreshed"query: "SELECT DATE_TRUNC('day', EventTime) AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 GROUP BY DATE_TRUNC('day', EventTime) ORDER BY DATE_TRUNC('day', EventTime)used_functions: [{  "func_name": "date_trunc",  "feature": "Truncates a date to a specified precision"}, {  "func_name": "count",  "feature": "Counts the number of rows or not-NULL values"}]Solve by breaking the problem into steps. First, Analyze the question; second, understand the schema and bind the question; third, write the Query, and then explain why we used the functions## Output Indicator` ``` `## Step 1: Analyze the Question...## Step 2: Understand the Schema...## Step 3: Write the SQL Query...## Step 4: Explanation of Functions Used...` ``` `

## Step 1: Analyze the Question

The question requires us to retrieve the page view count from a table called "hits" that has several columns. We need to filter the data by CounterID, date, and IsRefresh column. Finally, the query should group the data by the day and order it by the date.

## Step 2: Understand the Schema

The schema defines a table called "hits" with many columns that store information about the website visitors, their actions, and their devices. Each row represents a single event, and the table is sorted by CounterID, EventDate, UserID, EventTime, WatchID. The columns that are relevant to our query are:

- EventTime: the timestamp of the event
- CounterID: the ID of the counter that tracks the event
- IsRefresh: a flag that indicates whether the event was triggered by a page refresh
- EventDate: the date of the event

## Step 3: Write the SQL Query

Based on the above analysis, we can write the SQL query as follows:

` ``` `
SELECT DATE_TRUNC('day', EventTime) AS M, COUNT(*) AS PageViews
FROM hits
WHERE CounterID = 62
  AND EventDate >= '2013-07-14'
  AND EventDate <= '2013-07-15'
  AND IsRefresh = 0
GROUP BY DATE_TRUNC('day', EventTime)
ORDER BY DATE_TRUNC('day', EventTime)
` ``` `

## Step 4: Explanation of Functions Used

We used the following functions in our query:

- `DATE_TRUNC`: This function truncates a timestamp to a specified time unit (in our case, day) and returns a new timestamp. We use it to group the data by day.
- `COUNT`: This function counts the number of rows in each group. We use it to count the number of page views in each day.

Both functions are common in SQL queries and are used to manipulate and summarize data.

# 可以分两步
prompt-collector generate -c 2000 -d clickhouse -o ${{ ci.workspace }}/generated/ --concurrent 8 --timeout 3h
prompt-collector validate --input ${{ ci.workspace }}/generated/ -o /data/prompt-collector/test/dataset/validated --concurrent 4
# 也支持生成数据并校验
prompt-collector generate -c 2000 -d clickhouse -o ${{ ci.workspace }}/generated/ --validate --concurrent 8 --timeout 3h