SQL histogram Updated 2025-07-16
OK, there's a billion questions:
- SQL Server
- stackoverflow.com/questions/485409/generating-a-histogram-from-column-values-in-a-database OP did not know the difference between count and histogram :-) But it's the number one Google result.
- stackoverflow.com/questions/19103991/create-range-bins-from-sql-server-table-for-histograms has a minor extra group by twist, but otherwise fine
- stackoverflow.com/questions/16268441/generate-histogram-in-sql-server
- SQLite
- stackoverflow.com/questions/67514208/how-to-optimise-creating-histogram-bins-in-sqlite perf only, benchmarking would be needed. SQLite.
- stackoverflow.com/questions/32155449/create-a-histogram-with-a-dynamic-number-of-partitions-in-sqlite variable bin size, same number of entries per bin
- stackoverflow.com/questions/60348109/histogram-for-time-periods-using-sqlite-regular-buckets-1h-wide time
- MySQL: stackoverflow.com/questions/1764881/getting-data-for-histogram-plot MySQL appears to extend
ROUNDto also round by integers:ROUND(numeric_value, -2), but this is not widely portable which is a shame - stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql specifically asks about empty bins, which is amazing. Amazon Redshift dialect unfortunately, but answer provided works widely, and Redshift was forked from PostgreSQL, so there's hope. Those newb open source server focused projects that don't use AGPL!
Let's try it on SQLite 3.40.1, Ubuntu 23.04. Data setup:
sqlite3 tmp.sqlite 'create table t(x integer)'
sqlite3 tmp.sqlite <<EOF
insert into t values (
0,
2,
2,
3,
5,
6,
6,
8,
9,
17,
)
EOF
sqlite3 tmp.sqlite 'create index tx on t(x)'For a bin size of 5 ignoring empty ranges we can:which produces the desired:
sqlite3 tmp.sqlite <<EOF
select floor(x/5)*5 as x,
count(*) as cnt
from t
group by 1
order by 1
EOF0|4
5|5
15|1And to consider empty ranges we can use SQL which outputs the desired:
genenerate_series + as per stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql:sqlite3 tmp.sqlite <<EOF
select x, sum(cnt) from (
select floor(x/5)*5 as x,
count(*) as cnt
from t
group by 1
union
select *, 0 as cnt from generate_series(0, 15, 5)
)
group by x
EOF0|4
5|5
10|0
15|1sqlite3 Node.js package Updated 2025-07-16Includes its own copy of sqlite3, you don't use the system one, which is good to ensure compatibility. The version is shown at: github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/common-sqlite.gypi#L3 SQLite source is tracked compressed in-tree: github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/sqlite-autoconf-3360000.tar.gz horrendous. This explains why it takes forever to clone that repository. People who don't believe in git submodules, there's even an official Git mirror at: github.com/sqlite/sqlite
It appears to spawn its own threads via its C extension (since JavaScript is single threaded and and SQLite is not server-based), which allows for parallel queries using multiple threads: github.com/mapbox/node-sqlite3/blob/v5.0.2/src/threading.h
Hello world example: nodejs/node-sqlite3/index.js.
As of 2021, this had slumped back a bit, as maintainers got tired. Unmerged pull requests started piling more, and
better-sqlite3 Node.js package started pulling ahead a little.- github.com/mapbox/node-sqlite3/issues/1381
FATAL ERROR: Error::ThrowAsJavaScriptException napi_throwwith Node.jsworker_threadsvsbetter-sqlite3Node.js package github.com/JoshuaWise/better-sqlite3/issues/237
SQL RECURSIVE prevent infinite recursion Updated 2025-07-16
Example under: nodejs/sequelize/raw/tree.js
SQL tree traversal Updated 2025-07-16
Example: nodejs/sequelize/raw/tree.js
- Implementation agnostic
- Postgres
- stackoverflow.com/questions/67848017/simple-recursive-sql-query
- stackoverflow.com/questions/28688264/how-to-traverse-a-hierarchical-tree-structure-structure-backwards-using-recursiv
- stackoverflow.com/questions/51822070/how-can-postgres-represent-a-tree-of-row-ids
- depth first
- uspecified depth first variant
- preorder DFS
- breadth-first stackoverflow.com/questions/3709292/select-rows-from-table-using-tree-order
- MySQL
- Microsoft SQL Server
SQL TRIGGER Updated 2025-07-16
In particular, everything that happens in a trigger happens as if it were in a transaction. This way, you can do less explicit transactions when you use triggers. It is a bit like the advantages of SQL CASCADE.
DBMS:
SQL window
RANGE Updated 2025-07-16rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table t (id integer, val integer)"
sqlite3 tmp.sqlite <<EOF
insert into t values
(0, 0),
(1, 5),
(2, 10),
(3, 14),
(4, 15),
(5, 16),
(6, 20),
(7, 25),
(8, 29),
(9, 30),
(10, 30),
(11, 31),
(12, 35),
(13, 40)
EOFShow how many neighbours each column has with Output:
val between val - 2 and val + 2 inclusive:sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
ORDER BY val RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) FROM t;
EOF0|0|1
1|5|1
2|10|1
3|14|3
4|15|3
5|16|3
6|20|1
7|25|1
8|29|4
9|30|4
10|30|4
11|31|4
12|35|1
13|40|1val - 1 and val + 1 inclusive instead:sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t;
EOF0|0|1
1|5|1
2|10|1
3|14|2
4|15|3
5|16|2
6|20|1
7|25|1
8|29|3
9|30|4
10|30|4
11|31|3
12|35|1
13|40|1There seems to be no analogue to HAVING for window functions, so we can just settle for a subquery for once, e.g.:which outputs:
sqlite3 tmp.sqlite <<EOF
SELECT * FROM (
SELECT id, val, COUNT(*) OVER (
ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as c FROM t
) WHERE c > 2
EOF4|15|3
8|29|3
9|30|4
10|30|4
11|31|3 SQUID device Updated 2025-07-16
Superconducting Quantum Interference Device by Felipe Contipelli (2019)
Source. Good intuiotionistic video. Some points deserved a bit more detail.Mishmash of SQUID interviews and talks by Bartek Glowaki
. Source. The videos come from: www.ascg.msm.cam.ac.uk/lectures/. Vintage.
One of the segments is by John Clarke.
An experimental lab video for COVID-19 lockdown. Thanks, COVID-19. Presented by a cute and awkward Adam Stewart.
Uses a SQUID device and control system made by STAR Cryoelectronics. We can see Mr. SQUID EB-03 written on the probe and control box, that is their educational product.
As mentioned on the Mr. SQUID specs, it is a high-temperature superconductor, so liquid nitrogen is used.
He then measures the I-V curve on an Agilent Technologies oscilloscope.
Unfortunately, the video doesn't explain very well what is happening behind the scenes, e.g. with a circuit diagram. That is the curse of university laboratory videos: some of them assume that students will have material from other internal sources.
- youtu.be/ql2Yo5LgU8M?t=211 shows the classic voltage oscillations, presumably on a magnetic field sweep, and then he puts a magnet next to the device from outside the Dewar
- youtu.be/ql2Yo5LgU8M?t=253 demonstrates the formation of Shapiro steps. Inserts a Rohde & Schwarz signal generator into the Dewar to vary the flux. The result is not amazing, but they are visible somewhat.
Stabilizer (group) Updated 2025-07-16
If we pick k elements of the set, the stabilizer subgroup of those k elements is a subgroup of the given permutation group that keeps those elements unchanged.
Note that an analogous definition can be given for non-finite groups. Also note that the case for all finite groups is covered by the permutation definition since all groups are isomorphic to a subgroup of the symmetric group
TODO existence and uniqueness. Existence is obvious for the identity permutation, but proper subgroup likely does not exist in general.
Standard cell library Updated 2025-07-16
Basically what register transfer level compiles to in order to achieve a real chip implementation.
After this is done, the final step is place and route.
They can be designed by third parties besides the semiconductor fabrication plants. E.g. Arm Ltd. markets its Artisan Standard Cell Libraries as mentioned e.g. at: web.archive.org/web/20211007050341/https://developer.arm.com/ip-products/physical-ip/logic This came from a 2004 acquisition: www.eetimes.com/arm-to-acquire-artisan-components-for-913-million/, obviously.
The standard cell library is typically composed of a bunch of versions of somewhat simple gates, e.g.:and so on.
Each of those gates has to be designed by hand as a 3D structure that can be produced in a given fab.
Simulations are then carried out, and the electric properties of those structures are characterized in a standard way as a bunch of tables of numbers that specify things like:Those are then used in power, performance and area estimates.
Stars nearest to the Sun Updated 2025-07-16
Some notable ones:
Star Trek: The Next Generation Updated 2025-07-16
Ciro Santilli likes this.
He doesn't have the patience to actually watch full episodes with rare exceptions, rather just watching selected scenes from: www.youtube.com/channel/UCdeIGY2DIjpGf0A2m9GSE3g, but still, it is interesting.
What appeals to Ciro in this series is how almost nothing is solved by violence, almost everything is decided in the bridge, at the "cerebral" level of the command structure. This reminds Ciro of a courtroom of law sometimes.
Maybe there's also a bit of 90's nostalgia involved too though, as this is something that would show on some random cable channels a bored young Ciro would have browsed during weekends, never really watching full episodes.
One crime of many episodes is being completely based on some stupid new scientific concept, which any character to back it up.
Another thing that hurt is that due to their obsession with the senior members of the crew, sometimes those senior members are sent in ridiculously risky operations, which is very unrealistic.
Episodes that Ciro watched fully and didn't regret:
- s02e09 Measure of a man en.wikipedia.org/wiki/The_Measure_of_a_Man_(Star_Trek:_The_Next_Generation) see also physics and the illusion of life
- s04e14 Clues en.wikipedia.org/wiki/Clues_(Star_Trek:_The_Next_Generation)
- s04e15 First contact en.wikipedia.org/wiki/First_Contact_(Star_Trek:_The_Next_Generation). Although the premise that the aliens look so much like humans, and worse, that Decker could speak their language to the point of passing as one of their race is preposterous, the idea of inversion of first contact is just too cute.
- s07e15 The Lower Decks en.wikipedia.org/wiki/Lower_Decks_(Star_Trek:_The_Next_Generation), sliding scale of idealism vs. cynicism near cynincism, yes please
Semi worth it:
- s03e11 The Hunted memory-alpha.fandom.com/wiki/The_Hunted_(episode) if it weren't for the ending, maybe this would have been decent
Not worth it:
- Cause and effect
TODO
- s06e11 Chain of command
STED microscopy Updated 2025-07-16
Instead of shining a light over the entire sample to saturate it, you illuminate just a small bit instead.
He was basically saying that this truly brings the resolution to the actual physical limits, going much much beyond 2014 Nobel prize levels.
Street reclamation Updated 2025-07-16
Only people who need to drive a car should be allowed to drive a car anywhere near a city, e.g. people who work door to door, people who are disabled, etc.
Countryside driving is fine. If going to a city, you just have to drive to a parking outside of the city where you then take the public transport. And those who live in cities must leave their cars there too.
Everyone else must walk or cycle from home to public transport.
Cars just destroy everything, they make everything ugly:
- this was extremely clear to Ciro Santilli as a cyclist. He previously lived in a place with few cars and the countryside was so pleasant. Then he moved to a place with more cars and it was shocking. It's a mixture of pollution, noise, and the fact that roads cut up the countryside that just make things not pleasant at all. Dual lane roads in particular are just a terrible thing. You can hear them from afar, much before you see them.
- even within cities, cars completely dehumanize the streets. For example, Ciro once lived in a small dead end street, and he would have gladly opened his front window more often to meet the neighbours. But just the noise of cars passing by every so often makes it impractical to work like that.
The Zatoichi effect applies well to the problem of cyclists:This is the main drama faced by cyclists.
- they are not really pedestrians, and pedestrian paths are not suitable to them because they are too narrow, of not smooth, or curved. But pedestrians will always have enough political power to have their paths, because they live around the paths
- they are not really motor vehicles, because motor vehicle paths are too wide and too fast for them. But motor vehicles will always have enough political power to have their paths, because people are lazy and stupid, and because as the world stands, individually you just don't have any reasonable choice to go anywhere.
Lobbying groups:
stress-ng Updated 2025-07-16
Sun Microsystems Updated 2025-07-16
Although Ciro Santilli is a bit past their era, there's an aura of technical excellence about those people. It just seems that they sucked at business. Those open source hippies. Erm, wait.
Bibliography:
- archive.org/details/sunburstascentof00hall Sunburst: the ascent of Sun Microsystems by Mark Hall (1990)
Sun Wukong Updated 2025-07-16
Only after Ciro became an adult did he finally understand that Sun Wukong was the basis for Dragon Ball as mentioned at: Figure "19th century illustration of the Journey to the West protagonist Sun Wukong". And that Sun Wukong was a million times more famous overall. Mind blown.
His given name "Wukong" literally means "the one who mastered the void" (Wu = Comprehend, Kong = void), so clearly a Dharma name. Edit: it is actually given in the novel, he was born without name. They seem to be Taoist however.
The family name sun1 孙 is the same character as "grandson", but most educated Chinese people seem to be able to recognize it as a reference to "monkey" from some archaic context not anymore in current usage.
Superconducting magnet Updated 2025-07-16
Applications: produce high magnetic fields forAs of the early 2020s, superconducting magnets predominantly use low temperature superconductors Nb-Ti and Nb-Sn, see also most important superconductor materials, but there were efforts underway to create practical high-temperature superconductor-based magnets as well: Section "High temperature superconductor superconducting magnet".
- magnetic resonance imaging, the most important commercial application as of the early 2020s
- more researchy applications as of the early 2020s:
Wikipedia has done well for once:
The current to the coil windings is provided by a high current, very low voltage DC power supply, since in steady state the only voltage across the magnet is due to the resistance of the feeder wires. Any change to the current through the magnet must be done very slowly, first because electrically the magnet is a large inductor and an abrupt current change will result in a large voltage spike across the windings, and more importantly because fast changes in current can cause eddy currents and mechanical stresses in the windings that can precipitate a quench (see below). So the power supply is usually microprocessor-controlled, programmed to accomplish current changes gradually, in gentle ramps. It usually takes several minutes to energize or de-energize a laboratory-sized magnet.
Superconducting qubits are good because superconductivity is macroscopic Updated 2025-07-16
Superconducting qubits are regarded as promising because superconductivity is a macroscopic quantum phenomena of Bose Einstein condensation, and so as a macroscopic phenomena, it is easier to control and observe.
This is mentioned e.g. in this relatively early: physicsworld.com/a/superconducting-quantum-bits/. While most quantum phenomena is observed at the atomic scale, superconducting qubits are micrometer scale, which is huge!
Physicists are comfortable with the use of quantum mechanics to describe atomic and subatomic particles. However, in recent years we have discovered that micron-sized objects that have been produced using standard semiconductor-fabrication techniques – objects that are small on everyday scales but large compared with atoms – can also behave as quantum particles.
Superconducting tunnel junction Updated 2025-07-16
Specific type of Josephson junction. Probably can be made tiny and in huge numbers through photolithography.
Superconductivity Updated 2025-07-16
Experiments:
- "An introduction to superconductivity" by Alfred Leitner originally published in 1965, source: www.alfredleitner.com/
- Isotope effect on the critical temperature. hyperphysics.phy-astr.gsu.edu/hbase/Solids/coop.html mentions that:
If electrical conduction in mercury were purely electronic, there should be no dependence upon the nuclear masses. This dependence of the critical temperature for superconductivity upon isotopic mass was the first direct evidence for interaction between the electrons and the lattice. This supported the BCS Theory of lattice coupling of electron pairs.
20. Fermi gases, BEC-BCS crossover by Wolfgang Ketterle (2014)
Source. Part of the "Atomic and Optical Physics" series, uploaded by MIT OpenCourseWare.Actually goes into the equations.
Notably, youtu.be/O_zjGYvP4Ps?t=3278 describes extremely briefly an experimental setup that more directly observes pair condensation.
The Map of Superconductivity by Domain of Science
. Source. Lacking as usual, but this one is particularly good as the author used to work on the area as he mentions in the video.Lecture notes:
Transition into superconductivity can be seen as a phase transition, which happens to be a second-order phase transition.
Unlisted articles are being shown, click here to show only listed articles.