In all the systems we use, the analytical charts on the dashboard always appear first. We develop many analytical charts for different users to quickly analyze their daily work. A better approach is to create a chart system that can be configured with data. We call it BI, which is commonly seen in tools like Power BI, Tableau, and others. Using BI tools, we configure charts one by one by setting up options such as tables, fields, relationships, aggregations, and filters. This process is often very complex and not easy to understand. I often wonder if there is a way to automatically generate charts just by telling a system what data I want. ChatGPT makes this possible. We just need to tell ChatGPT the structure and relationships of the tables, and then have it return the SQL based on our description to achieve this amazing feature. Yes, it's that simple! Let's try it. We will use the database of WordPress as an example. First, we need to convert the WordPress database DDL into simpler information, keeping only the necessary details: prompt:Format 1:
<div>{tablename} has {field1}, {field2}...
</div><div>{tablename2} has {field1}, {field2}...
</div><div>{tablename} and {tablename2} are related by field2
</div><div>Please convert the following DDL SQL to format 1:
</div><div>CREATE TABLE wp_commentmeta (
</div><div>meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
</div><div>comment_id bigint(20) unsigned NOT NULL DEFAULT '0',
</div><div>meta_key varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
</div><div>meta_value longtext COLLATE utf8mb4_unicode_520_ci,
</div><div>PRIMARY KEY (meta_id),
</div><div>KEY comment_id (comment_id),
</div><div>KEY meta_key (meta_key(191))
</div><div>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
</div><div>CREATE TABLE wp_comments (
</div><div>comment_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
</div><div>comment_post_ID bigint(20) unsigned NOT NULL DEFAULT '0',
</div><div>comment_author tinytext COLLATE utf8mb4_unicode_520_ci NOT NULL,
</div><div>comment_author_email varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
</div><div>comment_author_url varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
</div><div>...</div><div>
</div><div>
</div> Chat on the above prompt, reply as below: prompt:wp_commentmeta has meta_id, comment_id, meta_key, meta_value
<div>wp_comments has comment_ID, comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_karma, comment_approved, comment_agent, comment_type, comment_parent, user_id
</div><div>wp_commentmeta and wp_comments are related by comment_id
</div><div>wp_links has link_id, link_url, link_name, link_image, link_target, link_description, link_visible, link_owner, link_rating, link_updated, link_rel, link_notes, link_rss
</div><div>wp_options has option_id, option_name, option_value, autoload
</div><div>wp_postmeta has meta_id, post_id, meta_key, meta_value
</div><div>wp_posts has ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count
</div><div>wp_postmeta and wp_posts are related by post_id
</div><div>wp_term_relationships has object_id, term_taxonomy_id, term_order
</div><div>wp_term_taxonomy has term_taxonomy_id, term_id, taxonomy, description, parent, count
</div><div>wp_termmeta has meta_id, term_id, meta_key, meta_value
</div><div>wp_terms has term_id, name, slug, term_group
</div><div>wp_usermeta has umeta_id, user_id, meta_key, meta_value
</div><div>wp_users has ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name</div> Alright, next we will use the following prompt to generate an executable SQL: prompt:Format 1:
<div>{tablename} has {field1}, {field2}...
</div><div>{tablename2} has {field1}, {field2}...
</div><div>{tablename} and {tablename2} are related by field2
</div><div>Tables:
</div><div>wp_commentmeta has meta_id, comment_id, meta_key, meta_value
</div><div>wp_comments has comment_ID, comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_karma, comment_approved, comment_agent, comment_type, comment_parent, user_id
</div><div>wp_commentmeta and wp_comments are related by comment_id
</div><div>wp_links has link_id, link_url, link_name, link_image, link_target, link_description, link_visible, link_owner, link_rating, link_updated, link_rel, link_notes, link_rss
</div><div>wp_options has option_id, option_name, option_value, autoload
</div><div>wp_postmeta has meta_id, post_id, meta_key, meta_value
</div><div>wp_posts has ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count
</div><div>wp_postmeta and wp_posts are related by post_id
</div><div>wp_term_relationships has object_id, term_taxonomy_id, term_order
</div><div>wp_term_taxonomy has term_taxonomy_id, term_id, taxonomy, description, parent, count
</div><div>wp_termmeta has meta_id, term_id, meta_key, meta_value
</div><div>wp_terms has term_id, name, slug, term_group
</div><div>wp_usermeta has umeta_id, user_id, meta_key, meta_value
</div><div>wp_users has ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name
</div><div>The SQL for "Top 10 most commented posts in last 30 days" is(only SQL, no any notes):</div><div>
</div><div>
</div> Chat on the above prompt, and reply as below: prompt:SELECT
<div>wp_posts.ID,
</div><div>wp_posts.post_title,
</div><div>COUNT(wp_comments.comment_ID) AS comment_count
</div><div>FROM
</div><div>wp_posts
</div><div>INNER JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_ID
</div><div>WHERE
</div><div>wp_posts.post_status = 'publish'
</div><div>AND wp_comments.comment_approved = 1
</div><div>AND wp_comments.comment_date_gmt > DATE_SUB(NOW(), INTERVAL 30 DAY)
</div><div>GROUP BY
</div><div>wp_posts.ID
</div><div>ORDER BY
</div><div>comment_count DESC
</div><div>LIMIT
</div><div>10;</div> This is just a simple demo and we haven't provided enough information. ChatGPT generated the above SQL using online resources. In normal situations, we would need to provide additional information. Here we are just presenting an idea, and you will need to continuously optimize it. Enjoy your journey of optimizing your prompt!
