[新機能]SQLコマンドでテキストや画像などマルチモーダルなデータ分析を可能にする「Cortex AISQL」を試してみた

create table if not exists insights as
with IMAGE_INSIGHTS as (
    select created_at,user_id,relative_path as ticket_id,img_file as input_file,file_url as input_file_url,
        AI_COMPLETE('pixtral-large', prompt('Summarize this issue shown in this screenshot in one concise sentence: {0}', img_file)) as summary, summary as content
    from images
    ),
    EMAIL_INSIGHTS as (
    select created_at,user_id,ticket_id::text as ticket_id,null as input_file,'' as input_file_url,content as content,
        AI_COMPLETE('claude-3-7-sonnet', prompt('Summarize this issue in one concise sentence. 
If the user mentioned anything related to music preference, please keep that information: {0}', content)) as summary
    from emails
    )
select 'Image' as source, created_at, user_id, ticket_id, input_file, input_file_url, content, summary 
from IMAGE_INSIGHTS
    union
select 'Email' as source, created_at, user_id, ticket_id, input_file, input_file_url, content, summary 
from EMAIL_INSIGHTS;

select 
    c.ticket_id,
    c.content as "CUSTOMER ISSUE",
    s.solution,
    c.created_at,
from
    INSIGHTS c
left join
    SOLUTION_CENTER_ARTICLES s
on AI_FILTER(prompt('You are provided a customer issue and a solution center article. Please check if the solution article can address customer concerns. Reminder to check if the error details are matching. Customer issues: {0}; \n\nSolution: {1}', content, s.solution))
order by created_at asc;

select 
    monthname(created_at) as month, 
    count(*) as total_tickets,
    count(distinct user_id) as unique_users,
    AI_AGG(summary,'Analyze all the support ticket reviews and provide a comprehensive list of all issues mentioned. 
Format your response as a bulleted list of issues with their approximate frequency in percentage.') as top_issues_reported,
    from (select * from insights order by random() limit 200)
    group by month
    order by total_tickets desc,month desc;

AGGREGATE_PROMPT = """
Analyze all the support ticket reviews and provide a comprehensive list of all issues mentioned. 
Format your response as a bulleted list of issues with their approximate frequency in percentage.
"""

sql = f"""
select 
    monthname(created_at) as month, 
    count(*) as total_tickets,
    count(distinct user_id) as unique_users,
    AI_AGG(summary,'{AGGREGATE_PROMPT}') as top_issues_reported,
    from (select * from insights order by random() limit 200)
    group by month
    order by total_tickets desc,month desc
"""

df = session.sql(sql).to_pandas()
rows_to_display = 1
for row in df[:rows_to_display].itertuples():
    st.subheader(f"Aggregated Insights for {row.MONTH}")
    st.metric("Total Tickets",row.TOTAL_TICKETS)
    st.metric("Unique Users", row.UNIQUE_USERS)
    st.subheader("Top Issues")
    st.markdown(row.TOP_ISSUES_REPORTED)
    st.divider()

df_long = df.melt(id_vars='MONTH', value_vars=['TOTAL_TICKETS', 'UNIQUE_USERS'],
                  var_name='Metric', value_name='Total')

chart = alt.Chart(df_long).mark_bar().encode(
    y=alt.Y('MONTH:N', sort='-x'),
    x=alt.X('Total:Q'),
    color=alt.Color('Metric:N', scale=alt.Scale(scheme='tableau10')),
    tooltip=['MONTH', 'Metric', 'Total']
).properties(height=300)

st.altair_chart(chart, use_container_width=True)

SET FILTER_PROMPT = '
I am trying to find if the customer has mentioned any music genre perference in their comment. 
Does this comment mentioning specific music genre preference from the customer?: {0}';

SET CLASSIFY_PROMPT = 'Please help me classify the music preference mentioned in this comment: ';
SET MUSIC_GENRES = 'Electronic/Dance Music (EDM), Jazz, Indie/Folk, Rock, Classical, World Music, Blues, Pop';

create table if not exists filtered as 
select * from 
        (select * from insights order by random() limit 500)
    where AI_FILTER(prompt($FILTER_PROMPT, summary));

select 
    source, 
    summary,
    AI_CLASSIFY($CLASSIFY_PROMPT || summary,SPLIT($MUSIC_GENRES, ','))['labels'][0] as classified_label
from filtered;