Customer sentiment analysis

Step 1: Create business tables

In the target database where the pgml plugin is installed, create a schema, create business tables, and insert test data.

-- Create a schema
CREATE SCHEMA testschema;

-- Create a table for raw chat records
CREATE TABLE testschema.chat_records (
    session_id VARCHAR(40),
    dialog_time TIMESTAMP,
    role VARCHAR(10),  -- Role: customer/service
    dialog_text TEXT
);

-- Create a table to record inference results
CREATE TABLE testschema.sentiment_raw_results (
   session_id character varying(40),
   raw_response text
);

-- Write test data
INSERT INTO testschema.chat_records VALUES
('S001', '2024-05-01 09:30:00', 'customer', 'Your product quality is terrible! It broke after only three days!'),
('S001', '2024-05-01 09:31:00', 'service', 'We are very sorry for the inconvenience. We can arrange a free replacement.'),
('S002', '2024-05-01 10:15:00', 'customer', 'Thank you for your patient guidance. The problem is perfectly solved.'),
('S003', '2024-05-01 11:20:00', 'customer', 'I need to check the logistics information for my order.');

Step 2: Use the model to perform inference on a single piece of data

1. Download the DeepSeek model to the Master.

   Note

   Currently, only the DeepSeek-R1-Distill-Qwen-7B model is supported. This model is free of charge.

   SELECT pgml.download_built_in_model('deepseek-ai/DeepSeek-R1-Distill-Qwen-7B');

2. Use the model to perform inference on a single piece of data.

   SELECT 
       pgml.transform(
           task => '{
               "task": "text-generation",
               "model": "deepseek-ai/DeepSeek-R1-Distill-Qwen-7B",
               "load_in_8bit": false,
               "device_map": "auto"
           }'::JSONB,
           inputs => ARRAY[
               'Analyze the customer sentiment in the following chat record. "Positive" means the user is very satisfied. "Negative" means there is a high risk of churn. "Neutral" means no emotional tendency is shown. Do not provide the thought process or reasons. Return only the result.
   For example:
   # User message: Hello, I would like to ask about your plans.
   # Sentiment: Neutral
   
   Now, rate based on the following input:
   # User message: '||'Thank you for your patient guidance. The problem is perfectly solved.'||'
   # Sentiment: </think>'
           ],
           args => '{
               "max_new_tokens": 10,
               "temperature": 0.1
           }'::JSONB
       ) AS response;

   Parameter description

   Parameter name	Description	Sub-parameter	Example value
   task	Specifies the pre-trained model and task type to use.	task: The task type.	task => '{ "task": "text-generation", "model": "deepseek-ai/DeepSeek-R1-Distill-Qwen-7B", "load_in_8bit": false, "device_map": "auto" }'::JSONB
   		model: The model name.
   		load_in_8bit: A parameter related to model loading and 8-bit quantization. Valid values: false: Disables 8-bit quantization. true: Enables 8-bit quantization.
   		device_map: A parameter related to model loading and device allocation. The value auto indicates that the framework automatically allocates different parts of the model to available devices to optimize memory usage and compute efficiency. This setting is ideal for large models or multi-device scenarios.
   args	Passes a set of configuration options to a logical unit, such as a function, stored procedure, API, or model.	max_new_tokens: Limits the number of tokens the model generates.	args => '{ "max_new_tokens": 10, "temperature": 0.1 }'::JSONB
   		temperature: The temperature coefficient, which controls the randomness of the generation. A smaller value produces more deterministic and stable results. This is suitable for scenarios that require stable and consistent results, such as Q&A systems and code generation. A larger value produces more random and diverse results. This is suitable for scenarios that require diverse and creative content, such as dialogue generation and story writing.
   inputs	The text to classify.	None	ARRAY['The product quality is very good']

Step 3: Use the model to process historical chat records

1. Download the DeepSeek model to the compute nodes (Segments).

   SELECT pgml.download_built_in_model('deepseek-ai/DeepSeek-R1-Distill-Qwen-7B') FROM gp_dist_random('gp_id');

2. Process the historical chat records.

   WITH model_output AS (
   SELECT 
       session_id,
       pgml.transform(
           task => '{
               "task": "text-generation",
               "model": "deepseek-ai/DeepSeek-R1-Distill-Qwen-7B",
               "load_in_8bit": false,
               "device_map": "auto"
           }'::JSONB,
           inputs => ARRAY[
               'Analyze the customer sentiment in the following chat record. "Positive" means the user is very satisfied. "Negative" means there is a high risk of churn. "Neutral" means no emotional tendency is shown. Do not provide the thought process or reasons. Return only the result.
   For example:
   # User message: Hello, I would like to ask about your plans.
   # Sentiment: Neutral
   
   Now, rate based on the following input:
   # User message: '|| string_agg(dialog_text, '; ')||'
   # Sentiment: </think>'
           ],
           args => '{
               "max_new_tokens": 10,
               "temperature": 0.1
           }'::JSONB
       ) AS response
   FROM chat_records
   WHERE role = 'customer'  -- Analyze only customer messages
   GROUP BY session_id
   )
   
   INSERT INTO testschema.sentiment_raw_results
   SELECT 
       session_id,
       response->0->0->>'generated_text' AS raw_response
   FROM model_output;

   Parameter description

   Parameter name	Description	Sub-parameter	Example value
   task	Specifies the pre-trained model and task type to use.	task: The task type.	task => '{ "task": "text-generation", "model": "deepseek-ai/DeepSeek-R1-Distill-Qwen-7B", "load_in_8bit": false, "device_map": "auto" }'::JSONB
   		model: The model name.
   		load_in_8bit: A parameter related to model loading and 8-bit quantization. Valid values: false: Disables 8-bit quantization. true: Enables 8-bit quantization.
   		device_map: A parameter related to model loading and device allocation. The value auto indicates that the framework automatically allocates different parts of the model to available devices to optimize memory usage and compute efficiency. This setting is ideal for large models or multi-device scenarios.
   args	Passes a set of configuration options to a logical unit, such as a function, stored procedure, API, or model.	max_new_tokens: Limits the number of tokens the model generates.	args => '{ "max_new_tokens": 10, "temperature": 0.1 }'::JSONB
   		temperature: The temperature coefficient, which controls the randomness of the generation. A smaller value produces more deterministic and stable results. This is suitable for scenarios that require stable and consistent results, such as Q&A systems and code generation. A larger value produces more random and diverse results. This is suitable for scenarios that require diverse and creative content, such as dialogue generation and story writing.
   inputs	The text to classify.	None	ARRAY['The product quality is very good']

   Query results

   SELECT * FROM testschema.sentiment_raw_results limit 1;

   The following result is returned.

   session_id |                                                                        raw_response
   ------------+----------------------------------------------------------------------------------------------------------------------------------------------
    S002       | Analyze the customer sentiment in the following chat record. "Positive" means the user is very satisfied. "Negative" means there is a high risk of churn. "Neutral" means no emotional tendency is shown. Do not provide the thought process or reasons. Return only the result.
               | For example:
               | # User message: Hello, I would like to ask about your plans.
               | # Sentiment: Neutral
               | 
               | Now, rate based on the following input:
               | # User message: 
               | Thank you for your patient guidance. The problem is perfectly solved.
               | # Sentiment: </think>
               | Positive
   (1 row)

Step 4: Convert to quantitative labels

Convert the results into risk levels to enable business follow-up.

WITH sentiment_results AS(
    SELECT split_part(
        raw_response,
        '</think>',
        (length(raw_response) - length(replace(raw_response, '</think>', '')))/8 + 1
    ) AS raw_label,
    session_id
    FROM testschema.sentiment_raw_results
)
SELECT 
    r.session_id,
    r.dialog_time,
    r.dialog_text,
    CASE 
        WHEN s.raw_label~'Negative' THEN 'High risk (priority follow-up required)'
        WHEN s.raw_label~'Positive' THEN 'Low risk (archive and monitor)'
        ELSE 'Medium risk (routine handling)'
    END AS risk_level
FROM testschema.chat_records r
JOIN sentiment_results s ON r.session_id = s.session_id
WHERE r.role = 'customer';

The following result is returned.

session_id	|    dialog_time	|    dialog_text	                |	risk_level		
----------------+-----------------------+-----------------------------------+-------------------
 S001		| 2024-05-01 09:30:00	| Your product quality is terrible! It broke after only three days!	| High risk (priority follow-up required)	
 S003		| 2024-05-01 11:20:00	| I need to check the logistics information for my order.		| Medium risk (routine handling)	
 S002		| 2024-05-01 10:15:00	| Thank you for your patient guidance. The problem is perfectly solved.	| Low risk (archive and monitor)	
(3 rows)