llama3

简介

llama3被官方评为迄今为止功能最强大的公开LLM。

Meta Llama 3是Meta公司开发的一系列新型模型，具有8B和70B两种参数大小(预训练或指令调整)。

Llama 3经过指令调优的模型经过微调和优化，专为对话/聊天用例而设计，在常见基准测试中表现优异，超越许多现有的开源聊天模型。

使用

打开命令行窗口，运行：

ollama run llama3

使用CURL示例：

curl -X POST http://localhost:11434/api/generate -d '{
  "model": "llama3",
  "prompt":"Why is the sky blue?"
 }'

wizardlm2

介绍

来自微软AI的最先进的大型语言模型，在复杂的聊天、多语言、推理和代理用例上提高了性能。

WizardLM-2是下一代最先进的大型语言模型，在复杂的聊天、多语言、推理和代理用例上具有改进的性能。这个家族包括三款最先进的模型。

• wizardlm2:7b：最快的模型，与10倍大的开源模型的性能相当。

• wizardlm2:8x22b：在微软内部对高度复杂任务的评价中，是最先进的模型，也是最优秀的开源LLM。

• wizardlm2:70b：具有其规模的顶层推理能力的模型(即将推出)。

使用

ollama run wizardlm2:7b

gemma

介绍

Gemma是由Google DeepMind构建的一系列轻量级、最先进的开放模型。它的灵感来自谷歌的双子座模型。

使用

Gemma有2b和7b两种参数：

ollama run gemma:2b

ollama run gemma:7b (default)

codegemma

介绍

CodeGemma是一组功能强大的轻量级模型，可以执行各种编码任务，如中间填充代码完成、代码生成、自然语言理解、数学推理和指令遵循。

优势

• 智能代码生成：完成代码行、函数，甚至生成整个代码块，无论您是在本地工作还是使用Google Cloud资源。

• 提高准确性：CodeGemma模型在主要来源于网络文件、数学和代码的5000亿令牌的英语数据上进行训练，在生成的代码中不仅在语法上更加正确，而且在语义上更有意义，可以减少错误并减少调试时间。

• 多语言熟练：支持Python、JavaScript、Java、Kotlin、C++、C#、Rust、Go等多种语言。

• 简化工作流程：将CodeGemma模型集成到您的开发环境中，减少模板代码的编写，更快地专注于有意义且具有差异性的代码编写。

使用

ollama run codegemma

qwen

介绍

Qwen1.5是阿里云的一系列大型语言模型，涵盖了0.5B到72B个参数，在大量数据上进行预训练，包括网络文本、书籍、代码等。

拥有6个模型：

• ollama run qwen:0.5b

• ollama run qwen:1.8b

• ollama run qwen:4b

• ollama run qwen:7b

• ollama run qwen:14b

• ollama run qwen:32b

• ollama run qwen:72b

在人类偏好的聊天模型中表现出显著的性能改进；基础模型和聊天模型均支持多语言；所有大小模型稳定支持32K上下文长度。

特色：

• 低成本部署：推断过程的最低内存要求低于2GB。

• 大规模高质量训练语料库：模型在超过2.2万亿令牌的数据上进行了预训练，包括中文、英文、多语言文本、代码和数学，涵盖了通用和专业领域。通过大量消融实验，优化了预训练语料库的分布。

• 良好性能：Qwen支持长上下文长度（在1.8b、7b和14b参数模型上为8K，在72b参数模型上为32K），并在多个中文和英文下游评估任务（包括常识、推理、代码、数学等）中显著超越了现有类似规模的开源模型，甚至在几个基准测试中超越了一些更大规模的模型。

• 更全面的词汇覆盖：与基于中英词汇的其他开源模型相比，Qwen使用了超过15万个令牌的词汇表。这种词汇表对多种语言更友好，使用户能够直接增强某些语言的功能而无需扩展词汇表。

• 系统提示：Qwen可通过系统提示实现角色扮演、语言风格转换、任务设定和行为设定。

使用

ollama run qwen

sqlcoder

介绍

SQLCoder是一个15B参数模型，是在基础StarCoder模型上进行微调的。在sql-eval框架上，它在自然语言转SQL生成任务中略微优于gpt-3.5-turbo，并优于流行的开源模型。它还显著优于text-davinci-003，后者的规模是它的十倍以上。

使用

ollama run sqlcoder

对于多行输入，请尝试使用以下三引号提示，注意：将{question}更改为要回答的SQL问题。例如，哪些产品能产生最多的销售额。

""" 
### Instructions:
Your task is to convert a question into a SQL query, given a Postgres database schema.
Adhere to these rules:
- **Deliberately go through the question and database schema word by word** to appropriately answer the question
- **Use Table Aliases** to prevent ambiguity. For example, `SELECT table1.col1, table2.col1 FROM table1 JOIN table2 ON table1.id = table2.id`.
- When creating a ratio, always cast the numerator as float

### Input:
Generate a SQL query that answers the question `{question}`.
This query will run on a database whose schema is represented in this string:
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY, -- Unique ID for each product
  name VARCHAR(50), -- Name of the product
  price DECIMAL(10,2), -- Price of each unit of the product
  quantity INTEGER  -- Current quantity in stock
);

CREATE TABLE customers (
   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer
   name VARCHAR(50), -- Name of the customer
   address VARCHAR(100) -- Mailing address of the customer
);

CREATE TABLE salespeople (
  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson
  name VARCHAR(50), -- Name of the salesperson
  region VARCHAR(50) -- Geographic sales region
);

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale
  product_id INTEGER, -- ID of product sold
  customer_id INTEGER,  -- ID of customer who made purchase
  salesperson_id INTEGER, -- ID of salesperson who made the sale
  sale_date DATE, -- Date the sale occurred
  quantity INTEGER -- Quantity of product sold
);

CREATE TABLE product_suppliers (
  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier
  product_id INTEGER, -- Product ID supplied
  supply_price DECIMAL(10,2) -- Unit price charged by supplier
);

-- sales.product_id can be joined with products.product_id
-- sales.customer_id can be joined with customers.customer_id
-- sales.salesperson_id can be joined with salespeople.salesperson_id
-- product_suppliers.product_id can be joined with products.product_id

### Response:
Based on your instructions, here is the SQL query I have generated to answer the question `{question}`:
```sql
"""

输出示例：

SELECT customers.customer_id,
       products.product_id,
(SUM(sales.quantity * product_suppliers.supply_price)) / (SELECT 
SUM(quantity)
FROM sales
WHERE 
sales.salesperson_id = '{chosen_salesperson}') as profit
FROM   sales
INNER JOIN customers on sales.customer_id = customers.customer_id
INNER JOIN products on sales.product_id = products.product_id
INNER JOIN product_suppliers on products.product_id = 
product_suppliers.product_id
WHERE  sales.salesperson_id = '{chosen_salesperson}'
GROUP BY customers.customer_id, products.product_id
HAVING COUNT(DISTINCT sales.sale_date) >= {min_days}
AND (SELECT SUM(quantity * product_suppliers.supply_price)
FROM   sales
WHERE  sales.salesperson_id = '{chosen_salesperson}'
and sales.customer_id = customers.customer_id
and sales.product_id = products.product_id) >= {min_total}
ORDER BY profit DESC;
```


### Reasoning

The query is broken down into two parts: the first one fetches information
about each sale made by a specific salesman (`{chosen_salesperson}`), and 
groups it by customer ID, product ID, and calculates how much money was 
earned from each sale. The second part then filters out these results that
occurred after a certain number of days (determined by the variable 
`min_days`) and had an amount of money equal to or more than a certain 
value (determined by the variable `min_total`). Finally, the query orders 
the results in descending order based on the ratio between earned money 
and total money.

The first part relies on several tables from the database: salespeople, 
products, customers, sales, and product_suppliers. The salespeople table 
contains information about each salesperson's ID; the products table 
contains information about each product's name and price; the customers 
table contains information about each customer's address; the sales table 
contains information about each sale, including the quantity sold, the ID 
of the product sold, and the ID of the customer who made the purchase; and
the product_suppliers table contains information about the unit price 
charged by each supplier.

The variables in the query can be interpreted as follows:

- `{chosen_salesperson}` is a placeholder for the name of the salesman 
whose performance is being evaluated. This value should be replaced with 
an actual salesman's name or ID number.
- `min_days` is the minimum number of days that must have passed since the
sale in order to qualify for inclusion in the calculation. For example, if
this variable were set to 30, then only sales made 30 days after the start
date would be included in the calculation. This value should also be 
replaced with an integer representing the minimum number of days.
- `min_total` is a threshold amount that must have been earned by the 
salesman for the sale to qualify for inclusion in the calculation. For 
example, if this variable were set to 1000, then only sales worth at least
$1000 would be included in the calculation. This value should also be 
replaced with an integer representing the minimum amount of money that 
must have been earned.

The output is a table containing three columns: customer ID, product ID, 
and profit per unit price (calculated as the total money earned divided by
the quantity sold). The rows in this table are sorted in descending order 
based on the ratio between earned money and total money. Rows that don't 
meet both of these conditions will be dropped from the output.

## Example

```
Input:
min_days = 30
min_total = 1000
chosen_salesperson = '<NAME>'

Output:
  customer_id | product_id |         profit
-------------+----------------------------+-----------------
987         |     456    | 2.1             
1029        |    1357    | 1.91            
89          |    1234    | 0.59            
```

## License

The database is released under the [Open Database 
License](https://opendatacommons.org/licenses/odbl/1-0/).