Upgrade to Pro — share decks privately, control downloads, hide ads and more …

20160730_dbstudychugoku_lt

 20160730_dbstudychugoku_lt

@twingo_b

July 30, 2016
Tweet

More Decks by @twingo_b

Other Decks in Technology

Transcript

  1. ྫ MySQL -> PostgreSQL Microsoft SQL Server -> Amazon Aurora

    Oracle -> Amazon Aurora Amazon Aurora -> Amazon RedShift
  2. Replicate ongoing changes(Change Data Capture:CDC)Λ࢖ͬͯɺ ࠩ෼సૹͯ͠ΈΔ RDS:MySQL5.7 -> DMS ->

    Redshift https://aws.amazon.com/about-aws/whats-new/2016/07/aws-database-migration-service-now-supports-continuous- data-replication-with-high-availability-enables-ssl-endpoints-and-adds-support-for-sap-ase-formerly-sap-sybase-ase/
  3. MySQL —- 1990೥ʹޏͬͨਓͷݱࡏڅ༩Ұཡ SELECT departments.dept_name, employees.last_name AS manager_last_name, salaries.salary, titles.title,

    ninety_hire_man_employees.* FROM ( SELECT * FROM employees.employees WHERE hire_date between '1990-01-01' AND '1990-12-31' AND gender = 'M' ) AS ninety_hire_man_employees LEFT JOIN employees.salaries ON ninety_hire_man_employees.emp_no = salaries.emp_no AND salaries.to_date = '9999-01-01' LEFT JOIN employees.titles ON ninety_hire_man_employees.emp_no = titles.emp_no AND titles.to_date = '9999-01-01' LEFT JOIN employees.dept_emp ON ninety_hire_man_employees.emp_no = dept_emp.emp_no AND dept_emp.to_date = '9999-01-01' LEFT JOIN employees.departments ON dept_emp.dept_no = departments.dept_no LEFT JOIN employees.dept_manager ON dept_emp.dept_no = dept_manager.dept_no AND dept_manager.to_date = '9999-01-01' LEFT JOIN employees.employees ON dept_manager.emp_no = employees.emp_no WHERE salaries.salary IS NOT NULL ORDER BY departments.dept_name, salaries.salary DESC; https://gist.github.com/twingo-b/c404b2b2d5e9e71d32c0a131c50ea8bc
  4. Redshift -- 1990೥ʹޏͬͨਓͷݱࡏڅ༩ҰཡΛऔಘ -- ෦ॺ͝ͱͷڅ༩TOP3Λऔಘ WITH ninety_hire_man_employees AS ( SELECT

    * FROM employees.employees WHERE hire_date BETWEEN '1990-01-01' AND '1990-12-31' AND gender = 'M' ), departments_and_manager AS ( SELECT departments.*, employees.last_name AS manager_last_name FROM employees.departments LEFT JOIN employees.dept_manager ON departments.dept_no = dept_manager.dept_no AND dept_manager.to_date = '9999-01-01' LEFT JOIN employees.employees ON dept_manager.emp_no = employees.emp_no ) SELECT * FROM ( SELECT departments_and_manager.dept_name, departments_and_manager.manager_last_name, RANK ( ) OVER ( PARTITION BY departments_and_manager.dept_name ORDER BY salaries.salary DESC ) AS saraly_rank, salaries.salary, titles.title, ninety_hire_man_employees.* FROM ninety_hire_man_employees LEFT JOIN employees.salaries ON ninety_hire_man_employees.emp_no = salaries.emp_no AND salaries.to_date = '9999-01-01' LEFT JOIN employees.titles ON ninety_hire_man_employees.emp_no = titles.emp_no AND titles.to_date = '9999-01-01' LEFT JOIN employees.dept_emp ON ninety_hire_man_employees.emp_no = dept_emp.emp_no AND dept_emp.to_date = '9999-01-01' LEFT JOIN departments_and_manager ON dept_emp.dept_no = departments_and_manager.dept_no WHERE salaries.salary IS NOT NULL ) WHERE saraly_rank <= 3; https://gist.github.com/twingo-b/96cc16b6479db9da89a46c3df3d9f6d8