Google\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067SQL\uff08Structured Query Language\uff09\u3092\u6d3b\u7528\u3059\u308b\u3053\u3068\u3067\u3001\u5927\u91cf\u306a\u30c7\u30fc\u30bf\u306e\u5206\u6790\u3084\u5916\u90e8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3068\u306e\u9023\u643a\u304c\u53ef\u80fd\u306b\u306a\u308a\u307e\u3059\u3002QUERY\u95a2\u6570\u306b\u3088\u308bSQL\u98a8\u306e\u69cb\u6587\u3067\u306e\u64cd\u4f5c\u304b\u3089\u3001Google Apps Script\uff08GAS\uff09\u3092\u4f7f\u3063\u305f\u672c\u683c\u7684\u306a\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u63a5\u7d9a\u3001BigQuery\u3068\u306e\u7d71\u5408\u307e\u3067\u3001\u69d8\u3005\u306a\u30ec\u30d9\u30eb\u3067SQL\u3092\u6d3b\u7528\u3067\u304d\u307e\u3059\u3002<\/p>\n
\u672c\u8a18\u4e8b\u3067\u306f\u3001\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067SQL\u3092\u4f5c\u6210\u30fb\u5b9f\u884c\u3059\u308b\u65b9\u6cd5\u304b\u3089\u3001\u5b9f\u52d9\u3067\u306e\u6d3b\u7528\u30b7\u30fc\u30f3\u3001\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u6700\u9069\u5316\u306e\u30c6\u30af\u30cb\u30c3\u30af\u307e\u3067\u3001\u30c7\u30fc\u30bf\u5206\u6790\u3092\u52b9\u7387\u5316\u3059\u308b\u5b9f\u8df5\u7684\u306a\u30ce\u30a6\u30cf\u30a6\u3092\u8a73\u3057\u304f\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n
Google\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067\u306f\u3001QUERY\u95a2\u6570\u3092\u4f7f\u3063\u3066SQL\u98a8\u306e\u69cb\u6587\u3067\u30c7\u30fc\u30bf\u64cd\u4f5c\u304c\u53ef\u80fd\u3067\u3001\u3055\u3089\u306bGoogle Apps Script\u3092\u901a\u3058\u3066\u5916\u90e8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3068SQL\u63a5\u7d9a\u3082\u3067\u304d\u307e\u3059\u3002<\/b><\/span>\u3053\u308c\u306b\u3088\u308a\u3001\u8868\u8a08\u7b97\u30bd\u30d5\u30c8\u306e\u67a0\u3092\u8d85\u3048\u305f\u9ad8\u5ea6\u306a\u30c7\u30fc\u30bf\u5206\u6790\u304c\u5b9f\u73fe\u3057\u307e\u3059\u3002<\/p>\n \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306b\u304a\u3051\u308bSQL\u6d3b\u7528\u306f\u3001\u4e3b\u306b3\u3064\u306e\u30ec\u30d9\u30eb\u3067\u5b9f\u88c5\u3067\u304d\u307e\u3059\u3002\u7b2c1\u30ec\u30d9\u30eb\u306f\u3001QUERY\u95a2\u6570\u306b\u3088\u308b\u7591\u4f3cSQL\u69cb\u6587\u3067\u306e\u64cd\u4f5c\u3067\u3059\u3002SELECT\u3001WHERE\u3001GROUP BY\u3001ORDER BY\u3068\u3044\u3063\u305f\u57fa\u672c\u7684\u306aSQL\u53e5\u304c\u4f7f\u7528\u3067\u304d\u3001\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u5185\u306e\u30c7\u30fc\u30bf\u3092\u67d4\u8edf\u306b\u62bd\u51fa\u30fb\u96c6\u8a08\u3067\u304d\u307e\u3059\u3002<\/p>\n \u7b2c2\u30ec\u30d9\u30eb\u3067\u306f\u3001Google Apps Script\uff08GAS\uff09\u306eJDBC\u30b5\u30fc\u30d3\u30b9\u3092\u4f7f\u3048\u3070\u3001MySQL\u3084PostgreSQL\u306a\u3069\u306e\u4e3b\u8981\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306bSQL\u30af\u30a8\u30ea\u3092\u9001\u4fe1\u3067\u304d\u307e\u3059\u3002\u305f\u3060\u3057\u3001Microsoft SQL Server\u306a\u3069\u4e00\u90e8\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3067\u306fVPN\u63a5\u7d9a\u306a\u3069\u8ffd\u52a0\u8a2d\u5b9a\u304c\u5fc5\u8981\u3067\u3059\u3002<\/p>\n \u7b2c3\u30ec\u30d9\u30eb\u306f\u3001BigQuery\u3084Google Cloud SQL\u3068\u306e\u9023\u643a\u3067\u3059\u3002Google Cloud\u306e\u30c7\u30fc\u30bf\u5206\u6790\u57fa\u76e4\u3068\u7d71\u5408\u3059\u308b\u3053\u3068\u3067\u3001\u30d3\u30c3\u30b0\u30c7\u30fc\u30bf\u898f\u6a21\u306e\u5206\u6790\u3092\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u304b\u3089\u5b9f\u884c\u3067\u304d\u307e\u3059\u3002<\/p>\n \u591a\u304f\u306e\u30e6\u30fc\u30b6\u30fc\u304c\u300c\u30b0\u30fc\u30b0\u30eb\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8 SQL\u300d\u3067\u691c\u7d22\u3059\u308b\u80cc\u666f\u306b\u306f\u3001Excel\u306e\u30d1\u30ef\u30fc\u30af\u30a8\u30ea\u306b\u76f8\u5f53\u3059\u308b\u6a5f\u80fd\u3084\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3068\u306e\u9023\u643a\u65b9\u6cd5\u3092\u63a2\u3057\u3066\u3044\u308b\u30cb\u30fc\u30ba\u304c\u3042\u308a\u307e\u3059\u3002\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306f\u3001\u3053\u308c\u3089\u306e\u30cb\u30fc\u30ba\u306b\u5bfe\u3057\u3066\u8907\u6570\u306e\u30a2\u30d7\u30ed\u30fc\u30c1\u3092\u63d0\u4f9b\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n QUERY\u95a2\u6570\u306f\u3001SQL\u306e\u4e00\u90e8\u69cb\u6587\u3092\u4f7f\u3063\u3066\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u5185\u306e\u30c7\u30fc\u30bf\u3092\u62bd\u51fa\u30fb\u96c6\u8a08\u3067\u304d\u308b\u95a2\u6570\u3067\u3059\u3002<\/b><\/span><\/p>\n \u6708\u6b21\u58f2\u4e0a\u306e\u96c6\u8a08\u30af\u30a8\u30ea\uff1a<\/p>\n =QUERY(\u58f2\u4e0a\u30c7\u30fc\u30bf!A:E,<\/p>\n “SELECT A, SUM(D)<\/p>\n WHERE C = ‘2025\u5e74’<\/p>\n GROUP BY A<\/p>\n ORDER BY SUM(D) DESC<\/p>\n LABEL SUM(D) ‘\u58f2\u4e0a\u5408\u8a08'”)<\/p>\n \u3053\u306e\u30af\u30a8\u30ea\u306f\u30012025\u5e74\u306e\u30c7\u30fc\u30bf\u3092\u90e8\u7f72\u5225\u306b\u96c6\u8a08\u3057\u3001\u58f2\u4e0a\u9806\u306b\u4e26\u3079\u66ff\u3048\u307e\u3059\u3002<\/b><\/span><\/p>\n QUERY\u95a2\u6570\u306eGROUP BY\u53e5\u3068\u96c6\u8a08\u95a2\u6570\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u3053\u3068\u3067\u3001\u8907\u96d1\u306a\u96c6\u8a08\u51e6\u7406\u3092SQL\u98a8\u306b\u5b9f\u884c\u3067\u304d\u307e\u3059\u3002<\/b><\/span>\u30d4\u30dc\u30c3\u30c8\u30c6\u30fc\u30d6\u30eb\u306b\u983c\u3089\u306a\u3044\u67d4\u8edf\u306a\u96c6\u8a08\u304c\u53ef\u80fd\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n QUERY\u95a2\u6570\u3067\u4f7f\u3048\u308b\u96c6\u8a08\u95a2\u6570\uff1a<\/p>\n \u90e8\u7f72\u00d7\u6708\u5225\u306e\u96c6\u8a08\u4f8b\uff1a<\/p>\n =QUERY(A:E,<\/p>\n “SELECT A, B, SUM(C), AVG(D)<\/p>\n WHERE E IS NOT NULL<\/p>\n GROUP BY A, B<\/p>\n ORDER BY A, B”)<\/p>\n A\u5217\u306b\u90e8\u7f72\u540d\u3001B\u5217\u306b\u6708\u3001C\u5217\u30fbD\u5217\u306b\u6570\u5024\u3092\u5165\u308c\u3066\u3001E\u5217\u306b\u306f\u5b8c\u4e86\u6642\u306e\u30c1\u30a7\u30c3\u30af\u306a\u3069\u3092\u5165\u308c\u308b\u3068\u3001C\u5217\u306e\u5408\u8a08\u3068D\u5217\u306e\u5e73\u5747\u3092\u90e8\u7f72\u2192\u6708\u306e\u9806\u306b\u8868\u793a\u3067\u304d\u307e\u3059\u3002<\/b><\/span><\/p>\n \u30d4\u30dc\u30c3\u30c8\u5f62\u5f0f\u3067\u306e\u51fa\u529b\uff1a<\/p>\n =QUERY(A:D,<\/p>\n “SELECT A, SUM(C)<\/p>\n WHERE B = 2025<\/p>\n GROUP BY A<\/p>\n PIVOT D”)<\/p>\n \u3053\u306e\u4f8b\u3067\u306f\u3001A\u5217\u3092\u884c\u3001D\u5217\u3092\u5217\u3068\u3057\u3066\u3001C\u5217\u306e\u5408\u8a08\u3092\u30af\u30ed\u30b9\u96c6\u8a08\u3057\u307e\u3059\u3002<\/b><\/span><\/p>\n CASE\u6587\u306e\u4ee3\u66ff\u624b\u6cd5\uff1a<\/p>\n =QUERY(A:C,<\/p>\n “SELECT A,<\/p>\n SUM(B) AS ‘\u58f2\u4e0a\u5408\u8a08’,<\/p>\n COUNT(B) AS ‘\u4ef6\u6570’,<\/p>\n SUM(B)\/COUNT(B) AS ‘\u5e73\u5747\u5358\u4fa1’<\/p>\n WHERE C >= DATE ‘2025-01-01’<\/p>\n GROUP BY A”)<\/p>\n Google Apps Script\uff08GAS\uff09\u306eJDBC\u30b5\u30fc\u30d3\u30b9\u3092\u4f7f\u7528\u3059\u308b\u3068\u3001\u5916\u90e8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u76f4\u63a5SQL\u30af\u30a8\u30ea\u3092\u9001\u4fe1\u3057\u3001\u7d50\u679c\u3092\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306b\u53d6\u308a\u8fbc\u3081\u307e\u3059\u3002<\/b><\/span>\u672c\u683c\u7684\u306a\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u9023\u643a\u304c\u5b9f\u73fe\u3057\u307e\u3059\u3002<\/p>\n \u57fa\u672c\u7684\u306a\u63a5\u7d9a\u30b3\u30fc\u30c9\uff1a<\/p>\n function connectToDatabase() {<\/p>\n \/\/ \u63a5\u7d9a\u60c5\u5831\u306e\u8a2d\u5b9a<\/p>\n const server = ‘your-server.com’;<\/p>\n const dbName = ‘your_database’;<\/p>\n const username = ‘your_username’;<\/p>\n const password = ‘your_password’;<\/p>\n const port = 3306;<\/p>\n \/\/ MySQL\u63a5\u7d9aURL\u306e\u69cb\u7bc9<\/p>\n const url = `jdbc:mysql:\/\/${server}:${port}\/${dbName}`;<\/p>\n \/\/ \u63a5\u7d9a\u306e\u78ba\u7acb<\/p>\n const conn = Jdbc.getConnection(url, username, password);<\/p>\n return conn;}<\/p>\n \u30c7\u30fc\u30bf\u53d6\u5f97\u306e\u5b9f\u88c5\uff1a<\/p>\n function executeQuery() {<\/p>\n const conn = connectToDatabase();<\/p>\n const stmt = conn.createStatement();<\/p>\n \/\/ SQL\u30af\u30a8\u30ea\u306e\u5b9f\u884c<\/p>\n const query = `<\/p>\n SELECT<\/p>\n customer_name,<\/p>\n order_date,<\/p>\n total_amount<\/p>\n FROM orders<\/p>\n WHERE order_date >= ‘2025-01-01’<\/p>\n ORDER BY total_amount DESC<\/p>\n LIMIT 100`;<\/p>\n const rs = stmt.executeQuery(query);<\/p>\n \/\/ \u7d50\u679c\u306e\u51e6\u7406<\/p>\n const data = [];<\/p>\n while (rs.next()) {<\/p>\n data.push([<\/p>\n rs.getString(1),<\/p>\n rs.getDate(2),<\/p>\n rs.getDouble(3)<\/p>\n ]);}<\/p>\n rs.close();<\/p>\n stmt.close();<\/p>\n conn.close();<\/p>\n return data;}<\/p>\n \u53d6\u5f97\u30c7\u30fc\u30bf\u306e\u51fa\u529b\uff1a<\/p>\n function importDataToSheet() {<\/p>\n const sheet = SpreadsheetApp.getActiveSheet();<\/p>\n const data = executeQuery();<\/p>\n \/\/ \u30d8\u30c3\u30c0\u30fc\u884c\u306e\u8a2d\u5b9a<\/p>\n const headers = [‘\u9867\u5ba2\u540d’, ‘\u6ce8\u6587\u65e5’, ‘\u5408\u8a08\u91d1\u984d’];<\/p>\n sheet.getRange(1, 1, 1, headers.length).setValues([headers]);<\/p>\n \/\/ \u30c7\u30fc\u30bf\u306e\u66f8\u304d\u8fbc\u307f<\/p>\n if (data.length > 0) {<\/p>\n sheet.getRange(2, 1, data.length, data[0].length).setValues(data);}<\/p>\n \/\/ \u66f8\u5f0f\u8a2d\u5b9a<\/p>\n sheet.getRange(‘B:B’).setNumberFormat(‘yyyy-mm-dd’);<\/p>\n sheet.getRange(‘C:C’).setNumberFormat(‘\u00a5#,##0’);<\/p>\n BigQuery\u3068\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3092\u9023\u643a\u3059\u308b\u3053\u3068\u3067\u3001\u30da\u30bf\u30d0\u30a4\u30c8\u898f\u6a21\u306e\u30c7\u30fc\u30bf\u306b\u5bfe\u3057\u3066SQL\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u3057\u3001\u7d50\u679c\u3092\u76f4\u63a5\u53d6\u308a\u8fbc\u3081\u307e\u3059\u3002<\/b><\/span>\u30d3\u30c3\u30b0\u30c7\u30fc\u30bf\u5206\u6790\u304c\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u304b\u3089\u53ef\u80fd\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n \u9ad8\u5ea6\u306a\u5206\u6790\u30af\u30a8\u30ea\u306e\u4f8b\uff1a<\/p>\n SELECT<\/p>\n DATE_TRUNC(order_date, MONTH) as month,<\/p>\n product_category,<\/p>\n SUM(revenue) as total_revenue,<\/p>\n COUNT(DISTINCT customer_id) as unique_customers,<\/p>\n AVG(order_value) as avg_order_value<\/p>\n FROM `project.dataset.sales_data`<\/p>\n WHERE order_date >= ‘2025-01-01’<\/p>\n GROUP BY month, product_category<\/p>\n ORDER BY month DESC, total_revenue DESC<\/p>\n \u53d6\u5f97\u3057\u305fSQL\u30af\u30a8\u30ea\u7d50\u679c\u3092\u3001\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306e\u6a5f\u80fd\u3068\u7d44\u307f\u5408\u308f\u305b\u308b\u3053\u3068\u3067\u3001\u3088\u308a\u9ad8\u5ea6\u306a\u5206\u6790\u3084\u30d3\u30b8\u30e5\u30a2\u30e9\u30a4\u30bc\u30fc\u30b7\u30e7\u30f3\u304c\u5b9f\u73fe\u3067\u304d\u307e\u3059\u3002<\/b><\/span>\u5b9f\u8df5\u7684\u306a\u6d3b\u7528\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002<\/p>\n \u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\u30c0\u30c3\u30b7\u30e5\u30dc\u30fc\u30c9\u306e\u69cb\u7bc9\uff1a<\/p>\n function createDashboard() {<\/p>\n const sheet = SpreadsheetApp.getActiveSheet();<\/p>\n \/\/ SQL\u3067\u30c7\u30fc\u30bf\u53d6\u5f97<\/p>\n const salesData = executeQuery();<\/p>\n \/\/\u66f8\u304d\u8fbc\u3080\u30c7\u30fc\u30bf\u7bc4\u56f2\u306e\u53d6\u5f97<\/p>\n const dataRange = sheet.getRange(1, 1, salesData.length, salesData[0].length);<\/p>\n dataRange.setValues(salesData);<\/p>\n \/\/ \u30d4\u30dc\u30c3\u30c8\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210<\/p>\n const pivotTableRange = sheet.getRange(“G1”);<\/p>\n const pivotTable = pivotTableRange.createPivotTable(dataRange);<\/p>\n pivotTable.addRowGroup(1)\u00a0 \/\/ \u30ab\u30c6\u30b4\u30ea\u5225<\/p>\n pivotTable.addColumnGroup(2)\u00a0 \/\/ \u6708\u5225<\/p>\n pivotTable.addValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM)}<\/p>\n SQL\u30c7\u30fc\u30bf\u306e\u62e1\u5f35\u51e6\u7406\uff1a<\/p>\n =ARRAYFORMULA(<\/p>\n IF(QUERY_RESULT > 10000,<\/p>\n “\u512a\u826f\u9867\u5ba2”,<\/p>\n “\u4e00\u822c\u9867\u5ba2”))<\/p>\n \u5927\u91cf\u306e\u30c7\u30fc\u30bf\u3092SQL\u3067\u6271\u3046\u969b\u306f\u3001\u30af\u30a8\u30ea\u306e\u6700\u9069\u5316\u3068\u30ad\u30e3\u30c3\u30b7\u30e5\u6226\u7565\u304c\u91cd\u8981\u3067\u3059\u3002<\/b><\/span>\u5b9f\u884c\u901f\u5ea6\u3092\u6539\u5584\u3059\u308b\u30c6\u30af\u30cb\u30c3\u30af\u3092\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n \u52b9\u7387\u7684\u306a\u30af\u30a8\u30ea\u4f5c\u6210\uff1a<\/p>\n \u30c7\u30fc\u30bf\u66f4\u65b0\u983b\u5ea6\u306b\u5fdc\u3058\u305f\u8a2d\u8a08\uff1a<\/p>\n function getCachedData() {<\/p>\n const cache = CacheService.getScriptCache();<\/p>\n let data = cache.get(‘sql_data’);<\/p>\n if (!data) {<\/p>\n \/\/ \u30ad\u30e3\u30c3\u30b7\u30e5\u304c\u306a\u3044\u5834\u5408\u306fSQL\u5b9f\u884c<\/p>\n data = executeQuery();<\/p>\n \/\/ 1\u6642\u9593\u30ad\u30e3\u30c3\u30b7\u30e5<\/p>\n cache.put(‘sql_data’, JSON.stringify(data), 3600);<\/p>\n } else {<\/p>\n data = JSON.parse(data);}<\/p>\n return data;}<\/p>\n \u5927\u91cf\u30c7\u30fc\u30bf\u306e\u5206\u5272\u51e6\u7406\uff1a<\/p>\n function batchProcess() {<\/p>\n const batchSize = 1000;<\/p>\n let offset = 0;<\/p>\n while (true) {<\/p>\n const query = `<\/p>\n SELECT * FROM large_table<\/p>\n LIMIT ${batchSize}<\/p>\n OFFSET ${offset}`;<\/p>\n const result = executeQuery(query);<\/p>\n if (result.length === 0) break;<\/p>\n processData(result);<\/p>\n offset += batchSize;<\/p>\n \/\/ \u51e6\u7406\u9593\u9694\u3092\u8a2d\u3051\u308b<\/p>\n Utilities.sleep(1000);}}<\/p>\n SQL\u30a4\u30f3\u30b8\u30a7\u30af\u30b7\u30e7\u30f3\u5bfe\u7b56\u3084\u30a8\u30e9\u30fc\u51e6\u7406\u306f\u3001\u5b89\u5168\u306a\u30b7\u30b9\u30c6\u30e0\u904b\u7528\u306b\u4e0d\u53ef\u6b20\u3067\u3059\u3002<\/b><\/span>\u30d1\u30e9\u30e1\u30fc\u30bf\u5316\u30af\u30a8\u30ea\u306e\u4f7f\u7528\u3001\u5165\u529b\u5024\u306e\u691c\u8a3c\u3001\u9069\u5207\u306a\u30a8\u30e9\u30fc\u30cf\u30f3\u30c9\u30ea\u30f3\u30b0\u306b\u3088\u308a\u3001\u30bb\u30ad\u30e5\u30a2\u306a\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u9023\u643a\u3092\u5b9f\u73fe\u3057\u307e\u3059\u3002<\/p>\n \u30d1\u30e9\u30e1\u30fc\u30bf\u5316\u30af\u30a8\u30ea\u306e\u5b9f\u88c5\u4f8b\uff1a<\/p>\n function safeQuery(customerId) {<\/p>\n const conn = connectToDatabase();<\/p>\n const stmt = conn.prepareStatement(<\/p>\n ‘SELECT * FROM customers WHERE id = ?’);<\/p>\n stmt.setInt(1, customerId);<\/p>\n const rs = stmt.executeQuery();<\/p>\n \/\/ \u7d50\u679c\u51e6\u7406}<\/p>\n \u307e\u305f\u3001\u63a5\u7d9a\u60c5\u5831\u306f\u74b0\u5883\u5909\u6570\u3084\u30d7\u30ed\u30d1\u30c6\u30a3\u30b5\u30fc\u30d3\u30b9\u3067\u7ba1\u7406\u3057\u3001\u30b3\u30fc\u30c9\u306b\u76f4\u63a5\u8a18\u8ff0\u3057\u306a\u3044\u3053\u3068\u304c\u91cd\u8981\u3067\u3059\u3002\u5b9a\u671f\u7684\u306a\u6a29\u9650\u30ec\u30d3\u30e5\u30fc\u3068\u30a2\u30af\u30bb\u30b9\u30ed\u30b0\u306e\u76e3\u8996\u3082\u5b9f\u65bd\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\n Google\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067\u306f\u3001QUERY\u95a2\u6570\u3092\u4f7f\u3063\u305fSQL\u306e\u3088\u3046\u306a\u7c21\u6613\u7684\u306a\u5206\u6790\u304b\u3089\u3001Google Apps Script\u3092\u4f7f\u3063\u305f\u5916\u90e8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u63a5\u7d9a\u3001BigQuery\u3068\u306e\u9023\u643a\u307e\u3067\u3001\u3055\u307e\u3056\u307e\u306a\u30ec\u30d9\u30eb\u3067SQL\u3092\u6d3b\u7528\u3067\u304d\u307e\u3059\u3002<\/p>\n QUERY\u95a2\u6570\u306f\u624b\u8efd\u306b\u96c6\u8a08\u3084\u62bd\u51fa\u3092\u884c\u3048\u308b\u4e00\u65b9\u3001GAS\u3084BigQuery\u3092\u4f7f\u3048\u3070\u696d\u52d9\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3084\u5927\u898f\u6a21\u30c7\u30fc\u30bf\u3092\u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\u3067\u5206\u6790\u3067\u304d\u307e\u3059\u3002\u3055\u3089\u306b\u30ad\u30e3\u30c3\u30b7\u30e5\u3084\u30d0\u30c3\u30c1\u51e6\u7406\u3092\u6d3b\u7528\u3059\u308c\u3070\u3001\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u3082\u6700\u9069\u5316\u53ef\u80fd\u3067\u3059\u3002<\/p>\n SQL\u306e\u77e5\u8b58\u3092\u6d3b\u304b\u3057\u3066\u3001\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3092\u30c7\u30fc\u30bf\u5206\u6790\u306e\u30c4\u30fc\u30eb\u3068\u3057\u3066\u6d3b\u7528\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\n","protected":false},"excerpt":{"rendered":" Google\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067SQL\uff08Structured Query Language\uff09\u3092\u6d3b\u7528\u3059\u308b\u3053\u3068\u3067\u3001\u5927\u91cf\u306a\u30c7\u30fc\u30bf\u306e\u5206\u6790\u3084\u5916\u90e8\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3068\u306e\u9023\u643a\u304c\u53ef\u80fd\u306b\u306a\u308a\u307e\u3059\u3002QUERY\u95a2\u6570\u306b\u3088\u308bSQL\u98a8\u306e\u69cb\u6587\u3067\u306e\u64cd\u4f5c\u304b\u3089\u3001G […]<\/p>\n","protected":false},"author":25,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[51],"tags":[],"class_list":["post-21632","post","type-post","status-publish","format-standard","hentry","category-excel"],"acf":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts\/21632","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/users\/25"}],"replies":[{"embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/comments?post=21632"}],"version-history":[{"count":10,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts\/21632\/revisions"}],"predecessor-version":[{"id":21765,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts\/21632\/revisions\/21765"}],"wp:attachment":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/media?parent=21632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/categories?post=21632"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/tags?post=21632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}QUERY\u95a2\u6570\u3067SQL\u98a8\u306e\u69cb\u6587\u3092\u4f7f\u3046\u57fa\u672c\u7684\u306a\u65b9\u6cd5\u306f\uff1f<\/h2>\n
\n\n
\n \n\u6761\u4ef6\u30bf\u30a4\u30d7<\/th>\n \u30af\u30a8\u30ea\u4f8b<\/th>\n \u8aac\u660e<\/th>\n<\/tr>\n<\/thead>\n \n \u6570\u5024\u6bd4\u8f03<\/td>\n WHERE B > 1000<\/td>\n B\u5217\u304c1000\u3088\u308a\u5927\u304d\u3044<\/td>\n<\/tr>\n \n \u6587\u5b57\u5217\u4e00\u81f4<\/td>\n WHERE A = ‘\u55b6\u696d\u90e8’<\/td>\n A\u5217\u304c\u55b6\u696d\u90e8\u3068\u5b8c\u5168\u4e00\u81f4<\/td>\n<\/tr>\n \n \u90e8\u5206\u4e00\u81f4<\/td>\n WHERE A CONTAINS ‘\u6771\u4eac’<\/td>\n A\u5217\u306b\u6771\u4eac\u3092\u542b\u3080<\/td>\n<\/tr>\n \n \u8907\u6570\u6761\u4ef6<\/td>\n WHERE B > 100 AND C < 50<\/td>\n AND\u6761\u4ef6<\/td>\n<\/tr>\n \n OR\u6761\u4ef6<\/td>\n WHERE A = ‘\u6771\u4eac’ OR A = ‘\u5927\u962a’<\/td>\n \u3044\u305a\u308c\u304b\u306b\u4e00\u81f4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u5b9f\u7528\u4f8b\uff1a\u58f2\u4e0a\u30c7\u30fc\u30bf\u306e\u5206\u6790<\/h3>\n
GROUP BY\u3068AGGREGATE\u95a2\u6570\u3067\u96c6\u8a08\u51e6\u7406\u3092\u884c\u3046\u306b\u306f\uff1f<\/h2>\n
\u5229\u7528\u53ef\u80fd\u306a\u96c6\u8a08\u95a2\u6570<\/h3>\n
\n\n
\n \n\u95a2\u6570<\/th>\n \u7528\u9014<\/th>\n \u4f7f\u7528\u4f8b<\/th>\n<\/tr>\n<\/thead>\n \n SUM()<\/td>\n \u5408\u8a08<\/td>\n SUM(B)<\/td>\n<\/tr>\n \n AVG()<\/td>\n \u5e73\u5747<\/td>\n AVG(C)<\/td>\n<\/tr>\n \n COUNT()<\/td>\n \u30ab\u30a6\u30f3\u30c8<\/td>\n COUNT(A)<\/td>\n<\/tr>\n \n MAX()<\/td>\n \u6700\u5927\u5024<\/td>\n MAX(D)<\/td>\n<\/tr>\n \n MIN()<\/td>\n \u6700\u5c0f\u5024<\/td>\n MIN(E)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u8907\u6570\u9805\u76ee\u3067\u306e\u30b0\u30eb\u30fc\u30d7\u5316<\/h3>\n
PIVOT\u53e5\u3067\u30af\u30ed\u30b9\u96c6\u8a08<\/h3>\n
\u6761\u4ef6\u4ed8\u304d\u96c6\u8a08\u306e\u5b9f\u88c5<\/h3>\n
Google Apps Script\u3067\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3068SQL\u63a5\u7d9a\u3059\u308b\u65b9\u6cd5\u306f\uff1f<\/h2>\n
STEP1\uff1a\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u63a5\u7d9a\u306e\u8a2d\u5b9a<\/h3>\n
STEP2\uff1aSQL\u30af\u30a8\u30ea\u306e\u5b9f\u884c<\/h3>\n
STEP3\uff1a\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3078\u306e\u66f8\u304d\u8fbc\u307f<\/h3>\n
\u5bfe\u5fdc\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u4e00\u89a7<\/h3>\n
\n
BigQuery\u3068\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3092\u9023\u643a\u3057\u3066SQL\u3092\u5b9f\u884c\u3059\u308b\u306b\u306f\uff1f<\/h2>\n
Connected Sheets\u306e\u6d3b\u7528<\/h3>\n
\n
\u30ab\u30b9\u30bf\u30e0SQL\u30af\u30a8\u30ea\u306e\u5b9f\u884c<\/h3>\n
\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u5b9f\u884c\u306e\u8a2d\u5b9a<\/h3>\n
\n
SQL\u30af\u30a8\u30ea\u7d50\u679c\u3092\u6d3b\u7528\u3057\u305f\u9ad8\u5ea6\u306a\u5206\u6790\u30c6\u30af\u30cb\u30c3\u30af\u306f\uff1f<\/h2>\n
\u52d5\u7684\u30c0\u30c3\u30b7\u30e5\u30dc\u30fc\u30c9\u306e\u4f5c\u6210<\/h3>\n
\u6761\u4ef6\u4ed8\u304d\u66f8\u5f0f\u3067\u306e\u53ef\u8996\u5316<\/h3>\n
\n
ARRAYFORMULA\u95a2\u6570\u3068\u306e\u7d44\u307f\u5408\u308f\u305b<\/h3>\n
\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u6700\u9069\u5316\u306e\u30d9\u30b9\u30c8\u30d7\u30e9\u30af\u30c6\u30a3\u30b9\u306f\uff1f<\/h2>\n
\u30af\u30a8\u30ea\u6700\u9069\u5316\u306e\u30dd\u30a4\u30f3\u30c8<\/h3>\n
\n\n
\n \n\u6700\u9069\u5316\u9805\u76ee<\/th>\n \u60aa\u3044\u4f8b<\/th>\n \u826f\u3044\u4f8b<\/th>\n<\/tr>\n<\/thead>\n \n \u5217\u306e\u9078\u629e<\/td>\n SELECT *<\/td>\n SELECT \u5fc5\u8981\u306a\u5217\u306e\u307f<\/td>\n<\/tr>\n \n \u6761\u4ef6\u6307\u5b9a<\/td>\n WHERE\u5f8c\u3067\u7d5e\u8fbc\u307f<\/td>\n WHERE\u53e5\u3092\u5177\u4f53\u7684\u306b<\/td>\n<\/tr>\n \n \u7d50\u5408\u9806\u5e8f<\/td>\n \u5927\u304d\u3044\u30c6\u30fc\u30d6\u30eb\u304b\u3089<\/td>\n \u5c0f\u3055\u3044\u30c6\u30fc\u30d6\u30eb\u304b\u3089<\/td>\n<\/tr>\n \n \u96c6\u8a08<\/td>\n \u5168\u30c7\u30fc\u30bf\u53d6\u5f97\u5f8c\u306b\u96c6\u8a08<\/td>\n GROUP BY\u3067\u4e8b\u524d\u96c6\u8a08<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u30ad\u30e3\u30c3\u30b7\u30e5\u6226\u7565<\/h3>\n
\u30d0\u30c3\u30c1\u51e6\u7406\u306e\u5b9f\u88c5<\/h3>\n
\u30bb\u30ad\u30e5\u30ea\u30c6\u30a3\u3068\u30a8\u30e9\u30fc\u30cf\u30f3\u30c9\u30ea\u30f3\u30b0\u306e\u5b9f\u88c5\u65b9\u6cd5<\/h2>\n
\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3067SQL\u3092\u6d3b\u7528\u3057\u3066\u9ad8\u5ea6\u306a\u30c7\u30fc\u30bf\u5206\u6790<\/h2>\n